Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Add a Export button / Function

Codes here !

Moderators: macek, egami, gesf

Add a Export button / Function

Postby troygraham » Fri Jul 26, 2013 7:40 pm

I would like to add a Export button / function to the following page so it exports table contents including headings to CSV file. Anyone that could help would be much appreciated. :)


Code: Select all
<?php
include("Global.inc.php");
include_once(FULL_INCLUDE_PATH ."common.inc.php");


ini_set('display_errors', 'On');
error_reporting(E_ALL ^ E_NOTICE);


if (!permits(ACCESS_ACCOUNTS_RECEIVABLE))
{
   exit();   
}


function dbGetInvoicedData($clientID, $showSelected=false)
{
   $clientCondition = "";
   if ($clientID != "" && $clientID != "0")
   {
      $clientCondition = "AND   INVOICE.CLIENT_ID = ".$clientID;
   }
   
   $sqlQuery = "SELECT    INVOICE.INVOICE_ID,
                     Client.Name         AS 'Client',
                     INVOICE.TOTAL_CENTS,
                     INVOICE.INVOICE_DATE,
                     STATUS.STATUS      AS 'Status',
                     currency
               FROM    INVOICE
               LEFT JOIN Client ON INVOICE.CLIENT_ID = Client.ClientID
               LEFT JOIN STATUS ON INVOICE.STATUS_ID = STATUS.STATUS_ID
               WHERE    INVOICE.STATUS_ID = ".STATUS_INVOICED."
               AND      INVOICE.CANCELLED_DATE IS NULL
               ".$clientCondition."
               ORDER BY Client.Name, INVOICE.INVOICE_ID";
//echo "$sqlQuery<BR><BR>";   
   return   db_get_result($sqlQuery);
}

function dbGetTotals($clientID)
{
   $clientCondition = "";
   if ($clientID != "" && $clientID != "0")
   {
      $clientCondition = "AND   INVOICE.CLIENT_ID = ".$clientID;
   }
   
   $sqlQuery = "select currency, sum(TOTAL_CENTS) as TOTAL_CENTS
               FROM    INVOICE
               LEFT JOIN Client ON INVOICE.CLIENT_ID = Client.ClientID
               LEFT JOIN STATUS ON INVOICE.STATUS_ID = STATUS.STATUS_ID
               WHERE    INVOICE.STATUS_ID = ".STATUS_INVOICED."
               AND      INVOICE.CANCELLED_DATE IS NULL
               ".$clientCondition."
               group by currency order by currency asc";
   return   db_get_result($sqlQuery);
}

function displayReport()
{
   global $db;
      
   $rsClients = dbGetAccountsReceivable();
   
   include("Header.inc");
   ?>
   <script language="javascript">
   <!--
   
   function selectAll()
   {
//      count = document.MyForm.invoice.length;
      for (i = 0; i < count; i++)
      {
         
      }
   }
   
   function SubmitToSelf()
   {
      document.MyForm.action = "<?php echo $_SERVER['PHP_SELF']; ?>?goto=client_change";
      document.MyForm.submit();
   }

   function confirmSubmit()
   {
   var agree=confirm("Are you sure you wish to continue?");
   if (agree)
      return true ;
   else
      return false ;
   }
   -->
   </script>
   
   <form name="MyForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   
   <br>
   <h1>Accounts Receivable</h1>
   <br />
   Client: <select name="client_id" onChange="SubmitToSelf()">
         <option value="0" <?php echo ($_REQUEST['client_id'] == 0) ? 'selected' : '' ?>> All Clients </option>
            <?php
            $firstID = "";
            $clientSelected = false;
            while ($row = $rsClients -> fetchRow())
            {
               if (strlen($firstID) == 0)
               {
                  $firstID = $row[0];   
               }
               
               $selected = "";
               if ($row[0] == $_REQUEST['client_id'])
               {
                  $clientSelected = true;
                  $selected = " SELECTED";
               }
               
               echo "<option value=\"".$row[0]."\"".$selected.">".$row[1]."</option>\n";
            }
            
            //if (!$clientSelected)
            //{
            //   $_REQUEST['client_id'] = $firstID;
            //}
            
            $rsInvoices = dbGetInvoicedData($_REQUEST['client_id']);            
            ?>
         </select>   
   </form>
   
   <form name="InvoiceForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
      <input type="hidden" name="client_id" value="<?php echo $_REQUEST['client_id']; ?>">
   <BR>   
   <table cellpadding=0 cellspacing=1 border=0 bordercolor=#888888 width=700>
   <?php
   $total = 0;
   $count = 0;
   while ($row = $rsInvoices -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $count++;
      if ($count == 1)
      {
         ?>
         <tr>
            <td class=TableHeader>Paid?</td>
            <td class=TableHeader>View</td>
            <td class=TableHeader>Invoice No.</td>
            <td class=TableHeader>Client</td>
            <td class=TableHeader>Amount</td>
            <td class=TableHeader>Currency</td>
            <td class=TableHeader>Date</td>
            <td class=TableHeader>Status</td>
            <td>&nbsp;</td>
         </tr>         
         <?php
      }
      
      $total += $row['TOTAL_CENTS'];
      
      echo "<tr>";
      echo "<td class=TableCells align=center><input type=checkbox name=\"invoice[".$row['INVOICE_ID']."]\"></td>";
      echo "<td class=TableCells align=center><!-- <a href=\"ViewInvoice.php?id=".$row['INVOICE_ID']."\" target=\"_blank\"> --> <a href=\"PDFInvoice.php?type=single&invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">View</a> <a href=\"PDFInvoice.php?type=summary&invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">S</a> <a href=\"PDFInvoice.php?invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">D</a></td>";
      echo "<td class=TableCells style=\"text-align:right\">".$row['INVOICE_ID']."</td>";
      echo "<td class=TableCells>".$row['Client']."</td>";
      echo "<td class=TableCells style=\"text-align:right\">$".addDecimalPoint($row['TOTAL_CENTS'])."</td>";
      echo "<td class=TableCells>".$row['currency']."</td>";
      echo "<td class=TableCells>".formatN14Date($row['INVOICE_DATE'])."</td>";
      echo "<td class=TableCells>".$row['Status']."</td>";
      echo "<td class=TableCells width=124><input type=\"submit\" name=\"contra_invoice_button[".$row['INVOICE_ID']."]\" value=\"Contra\" class=\"button60\" onClick=\"return confirmSubmit()\"><input type=\"submit\" name=\"cancel_invoice_button[".$row['INVOICE_ID']."]\" value=\"Cancel\" class=\"button60\"></td>";
      echo "</tr>\n";   
   }

   
   if ($count > 0)
   {
      
      $rsTotals = dbGetTotals($_REQUEST["client_id"]);
      while ($row = $rsTotals -> fetchRow(DB_FETCHMODE_ASSOC))
      {
            ?>   
      
            <tr>
         <td class=TableCells><b><?php echo $row['currency']; ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells style="text-align: right"><b>$<?php echo addDecimalPoint($row['TOTAL_CENTS']); ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
      </tr>
      <?php } ?>
               
      <tr>
         <td class=TableCells><b>Totals</b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells style="text-align: right"><b>$<?php echo addDecimalPoint($total); ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
      </tr>
      <?php
   }
   else
   {
      echo "There are currently no accounts receivable.";   
   }
   
   echo "</table>\n";   
   
   if ($count > 0)
   {
      ?>
      <br />
   
      <div align="center" style="width: 600px">
         <script language="JavaScript" src="datepicker.js">
      </script>
         <input type=text size=11 name=paid_date value="<?php echo date("d/m/Y"); ?>">
                  <a href="javascript:show_calendar('InvoiceForm.paid_date');"
                     onmouseover="window.status='Show Calendar';return true;"
                     onmouseout="window.status='';return true;">
                     <img src="images/calendar.gif" border="0" alt="Show Calendar" align="absmiddle"></a>
         <input type="submit" class="button" name="paid_button" value="Paid">
      </div>
      <?php
   }
   
   echo "</form>\n";

   include("Footer.inc");
}


function displayCancelConfirmForm($id)
{
   include("Header.inc");
   ?>
   <form name="InvoiceForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
      <input type="hidden" name="client_id" value="<?php echo $_REQUEST['client_id']; ?>">   
      
      <b><u>You are about to cancel invoice number <?php echo $id; ?></u></b>.<br>
      <br>
      All jobs in this invoice will be returned to status 'delivered/quoted'.  You may then adjust the billing
      details and reinvoice them.<br>
      <br>
      Are you sure?<br>
      <input type="submit" class="button_resizable" name="cancel_confirm_button[<?php echo $id; ?>]" value="Yes">
      <input type="submit" class="button_resizable" name="no_button" value="No">
      
   </form>
   <?php
   include("Footer.inc");
}

function processCancelConfirm($invoiceID)
{
   global $db;
   
   $rsJobs = dbGetInvoiceJobs($invoiceID);
   
   // Cancel the invoice
   $sqlQuery = "UPDATE INVOICE SET CANCELLED_DATE = ".date("YmdHis")."
               WHERE INVOICE_ID = ".$invoiceID;
   
   $db -> query($sqlQuery);
   
   // Return all jobs to 'delivered/quoted'   
   while ($row = $rsJobs -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_QUOTED."
                  WHERE JobId = ".$row['JOB_ID'];

      $db -> query($sqlQuery);
                  
   }
   
   header("location: ".$_SERVER['PHP_SELF']."?client_id=".$_REQUEST['client_id']);
}

function processContra($invoiceID)
{
   global $db;
   
   $arr_date_paid = explode("/", $_POST["paid_date"]);
   $int_date_paid = mktime(0,0,0,$arr_date_paid[1],$arr_date_paid[0],$arr_date_paid[2]);
   $date_paid = date("YmdHis");
   
   // Update the status of this invoice
   $sqlQuery = "UPDATE INVOICE SET STATUS_ID = ".STATUS_PAID.", PAID_DATE=$date_paid, IS_CONTRA=1 WHERE INVOICE_ID = ".$invoiceID;
   
   
   $db -> query($sqlQuery);
   
   // Now update the jobs
   $sqlQuery = "SELECT INVOICE_ID, JOB_ID FROM INVOICE_JOB WHERE INVOICE_ID = ".$invoiceID;
   
   $rs = db_get_result($sqlQuery);
   
   while ($row = $rs -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_PAID." WHERE JobID = ".$row['JOB_ID'];
      
      $db -> query($sqlQuery);                     
   }
   
   header("location: AccountsReceivable.php?client_id=".$_REQUEST['client_id']);
}

function processForm()
{
   global $db;
   
   if (isset($_POST['invoice']) && is_array($_POST['invoice']))
   {
      foreach ($_POST['invoice'] as $id => $value)
      {
         
         $arr_date_paid = explode("/", $_POST["paid_date"]);
         $int_date_paid = mktime(0,0,0,$arr_date_paid[1],$arr_date_paid[0],$arr_date_paid[2]);
         $date_paid = date("YmdHis", $int_date_paid);
         
         // Update the status of this invoice
         $sqlQuery = "UPDATE INVOICE SET STATUS_ID = ".STATUS_PAID.", PAID_DATE=$date_paid WHERE INVOICE_ID = ".$id;
         
         
         $db -> query($sqlQuery);
         
         // Now update the jobs
         $sqlQuery = "SELECT INVOICE_ID, JOB_ID FROM INVOICE_JOB WHERE INVOICE_ID = ".$id;
         
         $rs = db_get_result($sqlQuery);
         
         while ($row = $rs -> fetchRow(DB_FETCHMODE_ASSOC))
         {
            $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_PAID." WHERE JobID = ".$row['JOB_ID'];
            
            $db -> query($sqlQuery);            

            // Add a log entry
            addLog($row['JOB_ID'], LOG_TYPE_PAY);            
         }
   
         
      }
   }
   
   header("location: AccountsReceivable.php?client_id=".$_REQUEST['client_id']);
}




$db = DB_Connect();

if (isset($_GET['goto']) && $_GET['goto'] == "change_client")
{
   displayReport();   
}
else if (isset($_POST['paid_button']))
{
   processForm();
}
else if (isset($_POST['cancel_invoice_button']))
{
   displayCancelConfirmForm(key($_POST['cancel_invoice_button']));
}
else if (isset($_POST['cancel_confirm_button']))
{
   processCancelConfirm(key($_POST['cancel_confirm_button']));
}

else if (isset($_POST['contra_invoice_button']))
{
   processContra(key($_POST['contra_invoice_button']));
}
else
{
   displayReport();
}

$db->disconnect();

?>
troygraham
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Jul 26, 2013 7:33 pm

Re: Add a Export button / Function

Postby pbs » Fri Jul 26, 2013 8:14 pm

pbs
New php-forum User
New php-forum User
 
Posts: 39
Joined: Sat May 11, 2013 1:40 am
Location: Nashik, India

Re: Add a Export button / Function

Postby troygraham » Fri Jul 26, 2013 10:13 pm

I have attached a snapshot of the page.

How do I export the data from the table that is displayed on the page to a CSV file using a button next to the dropdown.

Will the class coding you sent me work in this scenario ?
Attachments
TableView.jpg
TableView.jpg (86.65 KiB) Viewed 505 times
troygraham
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Jul 26, 2013 7:33 pm

Re: Add a Export button / Function

Postby pbs » Fri Jul 26, 2013 10:30 pm

yes you need to add CSV export code
pbs
New php-forum User
New php-forum User
 
Posts: 39
Joined: Sat May 11, 2013 1:40 am
Location: Nashik, India

Re: Add a Export button / Function

Postby troygraham » Sat Jul 27, 2013 5:23 pm

I have inserted the Export code from the link you sent me and placed it into my page. How do I now get the export button to execute the new code and download csv file

Code: Select all
<?php
include("Global.inc.php");
include_once(FULL_INCLUDE_PATH ."common.inc.php");


ini_set('display_errors', 'On');
error_reporting(E_ALL ^ E_NOTICE);


if (!permits(ACCESS_ACCOUNTS_RECEIVABLE))
{
   exit();   
}

function __construct($sql,$filename)
{
   $Conn = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass)
                 or $this->error_msg("Error: Invalid MySQL Server Information");
   if (!$Conn)
      $this->error_msg("Error: Invalid MySQL Server Information");
   $DB_select = mysql_select_db($this->dbname, $Conn);
   if (!$DB_select)
      $this->error_msg("Error: Invalid MySQL Database");
   if ($this->temp_path == '')
      $this->temp_path = sys_get_temp_dir();
   $this->filename = $filename;
   $this->sql = $sql;         
   $this->export();
}

//sql execution function which will return resource id
function execute($sql)
{
   if ($sql!="")
   {
      $result = mysql_query($sql) or $this->error_msg("Error: Check MySQL Query($sql)");
      if ($result)
         return $result;
      else
         return false;
   }
}

//function to check number of record of resource id
function count_check($result)
{
   if ($result)
   {
      if (mysql_num_rows($result) > 0)
         return true;
      else
         return false;               
   }
}      

//function to fetch mysql data from resource id in associative array
function recordset($result)
{
   if ($result)
   {
      while($row = mysql_fetch_assoc($result))
         $data[] = $row;
   }
   return $data;
}

//function to display error message
function error_msg($msg)
{
   if ($msg != '')
      die($msg);
   else
      return true;
}

function export()
{
   $rs = $this->execute($this->sql);
   if (!$this->count_check($rs))
      $this->error_msg("Error: No Data");   
   else
   {
      $rs_data = $this->recordset($rs);
      if (is_array($rs_data) && count($rs_data) > 0)
      {
         $i = 0;
         foreach($rs_data as $rs_data_row)
         {
            if ($this->field_names == true)
            {
               if ($i == 0)
               {
                  $data_keys = array_keys($rs_data_row);
                  if (count($data_keys) > 0)
                     $this->data = implode($this->separator,$data_keys);
                  $this->data .= $this->line_break;

               }
               $t_str = '';
               foreach($data_keys as $dkey)
               {
                  $t_str .=  '"'.html_entity_decode($rs_data_row[$dkey]).'"'.$this->separator;
               }
               if ($t_str != '')
                  $this->data .= trim($t_str,$this->separator).$this->line_break;
            }
            $i++;
         }
         if ($this->data != '')            
         {
            if (file_put_contents($this->temp_path.$this->filename,$this->data) == FALSE)
            {
               $this->error_msg("Error: Cannot write to file(".$this->filename.")");
            }
            else
            {
// code to download file in csv format
header ("Content-type: octect/stream");
header ("Content-disposition: attachment; filename=".basename($this->temp_path.$this->filename));
header ("Content-Length: ".filesize($this->tenp_path.$this->filename));
readfile ($this->temp_path.$this->filename);
}
         }               
      }            
   }
}

$filename = 'AccountsReceivable.csv'; // download CSV filename
$field_name = "INVOICE_ID,CLIENT,DATE"; // database table fields which are to be exported in csv file
$sql = "SELECT    INVOICE.INVOICE_ID,
                     Client.Name         AS 'Client',
                     INVOICE.TOTAL_CENTS,
                     INVOICE.INVOICE_DATE,
                     STATUS.STATUS      AS 'Status',
                     currency
               FROM    INVOICE
               LEFT JOIN Client ON INVOICE.CLIENT_ID = Client.ClientID
               LEFT JOIN STATUS ON INVOICE.STATUS_ID = STATUS.STATUS_ID
               WHERE    INVOICE.STATUS_ID = ".STATUS_INVOICED."
               AND      INVOICE.CANCELLED_DATE IS NULL
               ".$clientCondition."
               ORDER BY Client.Name, INVOICE.INVOICE_ID"; // final SQL query


function dbGetInvoicedData($clientID, $showSelected=false)
{
   $clientCondition = "";
   if ($clientID != "" && $clientID != "0")
   {
      $clientCondition = "AND   INVOICE.CLIENT_ID = ".$clientID;
   }
   
   $sqlQuery = "SELECT    INVOICE.INVOICE_ID,
                     Client.Name         AS 'Client',
                     INVOICE.TOTAL_CENTS,
                     INVOICE.INVOICE_DATE,
                     STATUS.STATUS      AS 'Status',
                     currency
               FROM    INVOICE
               LEFT JOIN Client ON INVOICE.CLIENT_ID = Client.ClientID
               LEFT JOIN STATUS ON INVOICE.STATUS_ID = STATUS.STATUS_ID
               WHERE    INVOICE.STATUS_ID = ".STATUS_INVOICED."
               AND      INVOICE.CANCELLED_DATE IS NULL
               ".$clientCondition."
               ORDER BY Client.Name, INVOICE.INVOICE_ID";
//echo "$sqlQuery<BR><BR>";   
   return   db_get_result($sqlQuery);
}

function dbGetTotals($clientID)
{
   $clientCondition = "";
   if ($clientID != "" && $clientID != "0")
   {
      $clientCondition = "AND   INVOICE.CLIENT_ID = ".$clientID;
   }
   
   $sqlQuery = "select currency, sum(TOTAL_CENTS) as TOTAL_CENTS
               FROM    INVOICE
               LEFT JOIN Client ON INVOICE.CLIENT_ID = Client.ClientID
               LEFT JOIN STATUS ON INVOICE.STATUS_ID = STATUS.STATUS_ID
               WHERE    INVOICE.STATUS_ID = ".STATUS_INVOICED."
               AND      INVOICE.CANCELLED_DATE IS NULL
               ".$clientCondition."
               group by currency order by currency asc";
   return   db_get_result($sqlQuery);
}

function displayReport()
{
   global $db;
      
   $rsClients = dbGetAccountsReceivable();
   
   include("Header.inc");
   ?>
   <script language="javascript">
   <!--
   
   function selectAll()
   {
//      count = document.MyForm.invoice.length;
      for (i = 0; i < count; i++)
      {
         
      }
   }
   
   function SubmitToSelf()
   {
      document.MyForm.action = "<?php echo $_SERVER['PHP_SELF']; ?>?goto=client_change";
      document.MyForm.submit();
   }

   function confirmSubmit()
   {
   var agree=confirm("Are you sure you wish to continue?");
   if (agree)
      return true ;
   else
      return false ;
   }
   -->
   </script>
   
   <form name="MyForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   
   <br>
   <h1>Accounts Receivable</h1>
   <br />
   Client: <select name="client_id" onChange="SubmitToSelf()">
         <option value="0" <?php echo ($_REQUEST['client_id'] == 0) ? 'selected' : '' ?>> All Clients </option>
            <?php
            $firstID = "";
            $clientSelected = false;
            while ($row = $rsClients -> fetchRow())
            {
               if (strlen($firstID) == 0)
               {
                  $firstID = $row[0];   
               }
               
               $selected = "";
               if ($row[0] == $_REQUEST['client_id'])
               {
                  $clientSelected = true;
                  $selected = " SELECTED";
               }
               
               echo "<option value=\"".$row[0]."\"".$selected.">".$row[1]."</option>\n";
            }
            
            //if (!$clientSelected)
            //{
            //   $_REQUEST['client_id'] = $firstID;
            //}
            
            $rsInvoices = dbGetInvoicedData($_REQUEST['client_id']);            
            ?>
         </select>
   <input type="submit" name="Export" id="Export" value="Export" />
   
   </form>
   
   <form name="InvoiceForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
      <input type="hidden" name="client_id" value="<?php echo $_REQUEST['client_id']; ?>">
   <BR>   
   <table cellpadding=0 cellspacing=1 border=0 bordercolor=#888888 width=700>
   <?php
   $total = 0;
   $count = 0;
   while ($row = $rsInvoices -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $count++;
      if ($count == 1)
      {
         ?>
         <tr>
            <td class=TableHeader>Paid?</td>
            <td class=TableHeader>View</td>
            <td class=TableHeader>Invoice No.</td>
            <td class=TableHeader>Client</td>
            <td class=TableHeader>Amount</td>
            <td class=TableHeader>Currency</td>
            <td class=TableHeader>Date</td>
            <td class=TableHeader>Status</td>
            <td>&nbsp;</td>
         </tr>         
         <?php
      }
      
      $total += $row['TOTAL_CENTS'];
      
      echo "<tr>";
      echo "<td class=TableCells align=center><input type=checkbox name=\"invoice[".$row['INVOICE_ID']."]\"></td>";
      echo "<td class=TableCells align=center><!-- <a href=\"ViewInvoice.php?id=".$row['INVOICE_ID']."\" target=\"_blank\"> --> <a href=\"PDFInvoice.php?type=single&invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">View</a> <a href=\"PDFInvoice.php?type=summary&invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">S</a> <a href=\"PDFInvoice.php?invoiceID=".$row['INVOICE_ID']."\" target=\"_blank\">D</a></td>";
      echo "<td class=TableCells style=\"text-align:right\">".$row['INVOICE_ID']."</td>";
      echo "<td class=TableCells>".$row['Client']."</td>";
      echo "<td class=TableCells style=\"text-align:right\">$".addDecimalPoint($row['TOTAL_CENTS'])."</td>";
      echo "<td class=TableCells>".$row['currency']."</td>";
      echo "<td class=TableCells>".formatN14Date($row['INVOICE_DATE'])."</td>";
      echo "<td class=TableCells>".$row['Status']."</td>";
      echo "<td class=TableCells width=124><input type=\"submit\" name=\"contra_invoice_button[".$row['INVOICE_ID']."]\" value=\"Contra\" class=\"button60\" onClick=\"return confirmSubmit()\"><input type=\"submit\" name=\"cancel_invoice_button[".$row['INVOICE_ID']."]\" value=\"Cancel\" class=\"button60\"></td>";
      echo "</tr>\n";   
   }

   
   if ($count > 0)
   {
      
      $rsTotals = dbGetTotals($_REQUEST["client_id"]);
      while ($row = $rsTotals -> fetchRow(DB_FETCHMODE_ASSOC))
      {
            ?>   
      
            <tr>
         <td class=TableCells><b><?php echo $row['currency']; ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells style="text-align: right"><b>$<?php echo addDecimalPoint($row['TOTAL_CENTS']); ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
      </tr>
      <?php } ?>
               
      <tr>
         <td class=TableCells><b>Totals</b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells style="text-align: right"><b>$<?php echo addDecimalPoint($total); ?></b></td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
         <td class=TableCells>&nbsp;</td>
      </tr>
      <?php
   }
   else
   {
      echo "There are currently no accounts receivable.";   
   }
   
   echo "</table>\n";   
   
   if ($count > 0)
   {
      ?>
      <br />
   
      <div align="center" style="width: 600px">
         <script language="JavaScript" src="datepicker.js">
      </script>
         <input type=text size=11 name=paid_date value="<?php echo date("d/m/Y"); ?>">
                  <a href="javascript:show_calendar('InvoiceForm.paid_date');"
                     onmouseover="window.status='Show Calendar';return true;"
                     onmouseout="window.status='';return true;">
                     <img src="images/calendar.gif" border="0" alt="Show Calendar" align="absmiddle"></a>
         <input type="submit" class="button" name="paid_button" value="Paid">
      </div>
      <?php
   }
   
   echo "</form>\n";

   include("Footer.inc");
}


function displayCancelConfirmForm($id)
{
   include("Header.inc");
   ?>
   <form name="InvoiceForm" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
      <input type="hidden" name="client_id" value="<?php echo $_REQUEST['client_id']; ?>">   
      
      <b><u>You are about to cancel invoice number <?php echo $id; ?></u></b>.<br>
      <br>
      All jobs in this invoice will be returned to status 'delivered/quoted'.  You may then adjust the billing
      details and reinvoice them.<br>
      <br>
      Are you sure?<br>
      <input type="submit" class="button_resizable" name="cancel_confirm_button[<?php echo $id; ?>]" value="Yes">
      <input type="submit" class="button_resizable" name="no_button" value="No">
      
   </form>
   <?php
   include("Footer.inc");
}

function processCancelConfirm($invoiceID)
{
   global $db;
   
   $rsJobs = dbGetInvoiceJobs($invoiceID);
   
   // Cancel the invoice
   $sqlQuery = "UPDATE INVOICE SET CANCELLED_DATE = ".date("YmdHis")."
               WHERE INVOICE_ID = ".$invoiceID;
   
   $db -> query($sqlQuery);
   
   // Return all jobs to 'delivered/quoted'   
   while ($row = $rsJobs -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_QUOTED."
                  WHERE JobId = ".$row['JOB_ID'];

      $db -> query($sqlQuery);
                  
   }
   
   header("location: ".$_SERVER['PHP_SELF']."?client_id=".$_REQUEST['client_id']);
}

function processContra($invoiceID)
{
   global $db;
   
   $arr_date_paid = explode("/", $_POST["paid_date"]);
   $int_date_paid = mktime(0,0,0,$arr_date_paid[1],$arr_date_paid[0],$arr_date_paid[2]);
   $date_paid = date("YmdHis");
   
   // Update the status of this invoice
   $sqlQuery = "UPDATE INVOICE SET STATUS_ID = ".STATUS_PAID.", PAID_DATE=$date_paid, IS_CONTRA=1 WHERE INVOICE_ID = ".$invoiceID;
   
   
   $db -> query($sqlQuery);
   
   // Now update the jobs
   $sqlQuery = "SELECT INVOICE_ID, JOB_ID FROM INVOICE_JOB WHERE INVOICE_ID = ".$invoiceID;
   
   $rs = db_get_result($sqlQuery);
   
   while ($row = $rs -> fetchRow(DB_FETCHMODE_ASSOC))
   {
      $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_PAID." WHERE JobID = ".$row['JOB_ID'];
      
      $db -> query($sqlQuery);                     
   }
   
   header("location: AccountsReceivable.php?client_id=".$_REQUEST['client_id']);
}

function processForm()
{
   global $db;
   
   if (isset($_POST['invoice']) && is_array($_POST['invoice']))
   {
      foreach ($_POST['invoice'] as $id => $value)
      {
         
         $arr_date_paid = explode("/", $_POST["paid_date"]);
         $int_date_paid = mktime(0,0,0,$arr_date_paid[1],$arr_date_paid[0],$arr_date_paid[2]);
         $date_paid = date("YmdHis", $int_date_paid);
         
         // Update the status of this invoice
         $sqlQuery = "UPDATE INVOICE SET STATUS_ID = ".STATUS_PAID.", PAID_DATE=$date_paid WHERE INVOICE_ID = ".$id;
         
         
         $db -> query($sqlQuery);
         
         // Now update the jobs
         $sqlQuery = "SELECT INVOICE_ID, JOB_ID FROM INVOICE_JOB WHERE INVOICE_ID = ".$id;
         
         $rs = db_get_result($sqlQuery);
         
         while ($row = $rs -> fetchRow(DB_FETCHMODE_ASSOC))
         {
            $sqlQuery = "UPDATE Job SET STATUS_ID = ".STATUS_PAID." WHERE JobID = ".$row['JOB_ID'];
            
            $db -> query($sqlQuery);            

            // Add a log entry
            addLog($row['JOB_ID'], LOG_TYPE_PAY);            
         }
   
         
      }
   }
   
   header("location: AccountsReceivable.php?client_id=".$_REQUEST['client_id']);
}




$db = DB_Connect();

if (isset($_GET['goto']) && $_GET['goto'] == "change_client")
{
   displayReport();   
}
else if (isset($_POST['paid_button']))
{
   processForm();
}
else if (isset($_POST['cancel_invoice_button']))
{
   displayCancelConfirmForm(key($_POST['cancel_invoice_button']));
}
else if (isset($_POST['cancel_confirm_button']))
{
   processCancelConfirm(key($_POST['cancel_confirm_button']));
}

else if (isset($_POST['contra_invoice_button']))
{
   processContra(key($_POST['contra_invoice_button']));
}
else
{
   displayReport();
}

$db->disconnect();

?>
troygraham
New php-forum User
New php-forum User
 
Posts: 3
Joined: Fri Jul 26, 2013 7:33 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron