./pccstm/README100644 0 0 2213 7065522703 11570 0ustar rootwheel///////////////////////////////////////////////////////////////////////////// This is an attempt to port a msaccess wizard application to PHP. This application is very simple, it is my attempt to learn more SQL and PHP, you may or may not find it useful. My hope is that you will learn from the php code and sql statements used to port this application from msaccess. I'm including an application that I developed that will auto install this application and another script that will allow you to back your system. I had plans to port the address book sample db to php, however since I've created a similar app. I quit coding and removed those files but left the table giving you some data to play around with MySQL and PHP. To install: tar -xvzf pccstm-xx.tar.gz http://pathto/pccstm/install.php To backup your system http://pathto/pccstm/phpbackup.php To use the above module you need to allow the user your webserver is running under to have write access. Comments, bug fixes >> cthorn@wt.net Chauncey Thorn http://web.wt.net/~cthorn/ http://PCCS-Linux.COM/ cthorn@wt.net ///////////////////////////////////////////////////////////////////////////// ./pccstm/add_contact.php100644 0 0 1520 7035667401 13666 0ustar rootwheel

Edit Account Type

Account Type
"; print "$_footer"; ?> ./pccstm/display_trans.php100644 0 0 7144 7065251305 14302 0ustar rootwheelMAIN   |   Account Transactions\n"; print "
"; /* Display This Accounts Transactions */ $_qry_display_acct_trans =" SELECT accounts.*,account_type.*,transactions.* FROM accounts,account_type,transactions WHERE transactions.trans_acctid = accounts.acct_id AND accounts.acct_typeid = account_type.acct_typeid ORDER BY trans_date ASC "; $get_acct_trans = mysqlquery($db_name,"$_qry_display_acct_trans"); if(!empty($get_acct_trans)) { $num = mysql_numrows($get_acct_trans); $color1 = "#CCCCCC"; $color2 = "#CCCCCC"; // print "$_qry_display_acct_trans"; print "
"; print " "; for ($i=0; $i < $num; $i++): print ""; print ""; endfor; $query = "SELECT sum(trans_deposit) AS transdeposit, sum(trans_withdraw) AS transwithdraw FROM transactions"; $total_trans = mysqlquery($db_name,$query); $total_deposits = mysql_result($total_trans,0,"transdeposit"); $total_withdraw = mysql_result($total_trans,0,"transwithdraw"); $trans_balance = $total_deposits - $total_withdraw; print ""; print ""; print ""; print ""; // $trans_balance = setType($trans_balance,double); print ""; print ""; print "
Transaction Date Description Withdrawal Deposit Taxable
"; echo mysql_result($get_acct_trans,$i,"transactions.trans_number"); print ""; echo mysql_result($get_acct_trans,$i,"transactions.trans_date"); print ""; echo mysql_result($get_acct_trans,$i,"transactions.trans_description"); print ""; echo mysql_result($get_acct_trans,$i,"transactions.trans_deposit"); print ""; echo mysql_result($get_acct_trans,$i,"transactions.trans_withdraw"); print ""; echo mysql_result($get_acct_trans,$i,"transactions.trans_taxable"); print "
Totals:  \$$total_deposits\$$total_withdraw 
Balance:    \$ $trans_balance
"; print "
"; mysql_free_result($get_acct_trans); } else { echo mysql_error()."
\n"; } print "$_footer"; ?> ./pccstm/enter_trans.php100644 0 0 10104 7065244317 13765 0ustar rootwheel"; } else { echo mysql_error() . "
\n"; } endif; ?> Enter Transaction "; ?> MAIN   |   Transaction Entry \n"; print "
"; ?>
       
  Account Date: Description:
 
  Deposit Amt: Withdraw Amt: Taxable:
 
   

 
./pccstm/gen_date.ini100644 0 0 1662 7065242301 13157 0ustar rootwheel"; for($i=1; $i<13; $i++) { $nmonth = date("F", mktime(0,0,0,$i,1,$year)); if($i == $month) echo "\n"; else echo "\n"; } print ""; while (checkdate($month,$lastday,$year)) { $lastday++; } print ""; $i = $year; echo ""; } ?> ./pccstm/global.vars.php100644 0 0 4422 7065261502 13634 0ustar rootwheel$_title"; $_footer = ""; $header_trans_img = ""; // ////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////// // Database connection Information $db_server = "localhost"; $db_user = "root"; $db_passwd = ""; $db_name = "pccstm"; // Setup Database Connection $c = mysql_pconnect("$db_server","$db_user","$db_passwd") or die("Error"); if (empty($limit)) $limit=30; /////////////////////////////////////////////////////////////////////////////// // // Debug Mode 1=on 0=off used by function mysqlquery($query) // $debug_qry=1; $debug_qry=0; // // // Function used on ALL Queries function mysqlquery($db_name,$query) { global $debug_qry; $db_name= "pccstm"; if ($debug_qry!=0) echo $query."
"; $result = mysql($db_name,$query); return $result; } ////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////// // Standard method of running a query // Cut and Paste into code // 01-08-2000 cthorn // // $result = mysqlquery("$_qry_from_sqlqueries.php"); // if(!empty($result)) { // echo ""; // } else { // echo mysql_error()."
\n"; // } ///////////////////////////////////////////////////////////////////////////// ?> ./pccstm/header_trans.gif100644 0 0 4441 7065257730 14050 0ustar rootwheelGIF89a22I-@@Эu{z9;:697XbI}Opx13p4mdGT353KLKUB?P5 xxw\\[␐嫘=3"ܥԷסÒОӞ?:4PA0Мmy`Ð˘Ɣ̙ΚƢ|vnfLHDmXE}fP^N?qmhjgd_Be`XLDH+4l. ida7X.O7,9䮦紬HBA8&gW5:((`UU|{{Ǹ뛛tttlllbbbUUUHHHCCC!,22 H*\ȰÇa˕ Ä'(ȗ e|V.\rjE͙3f̨PHIc~rKφ;T%K֨Ȳ *7OQjUXX13 /g2ʔYj-̊\ *Pm߁CV ʵ0_Ƙ!1p[=2e0Iߜ9Ӈ9UU\i<:>&5oο9r\ԡ#M#(e''yuw%ⱌOCƫi@0 W)KXR@4^`:_ˢ.BAO?#S>"dPA:߈7p3.%K)$K1 v^<;(=vMYjc8ޘsʇ!L N>3-}61s+Te,|2`Pl8DM( 4pq9D,,/xqPMJPm7H,6.7 b"k>K(1HX 8^舄0Z> NH$(`8pZ! *2QO%dCEmT %,`7CN:,!肋3¶4;Ohi!. ǀ$qC(1# 5@)L fB ZP,h Ɓ/PadluPH%׎,a 'L [:刄 &\P#Ba yL" eBUB2 hADR iG3 'qK4# ex/sB)L<#s_"P)l c0 k8(8@KbC>3, ` #AD?` 9Y `_> aҰ`4` tlcjx@@%M 6`O2X q Ufa hA JZ\ðG0[C8نTcUD!Kː{\l ;./pccstm/index.php100644 0 0 1502 7065260517 12532 0ustar rootwheel "; print ""; print "

   $header_trans_img    "; print ""; print ""; print "Transaction Manager Menu    ::    $display_date

"; print ""; trans_manager(); ?> ./pccstm/install.php100644 0 0 5437 7065254444 13106 0ustar rootwheel $_title "; $_footer =""; // // ///////////////////////////////////////////////////////////////////////////////////// // print "$_header"; print "

$_title : PHP Application Install

"; print "

"; print "
"; print "

"; if($argv[0] == ""): print "

"; print "
"; print "PUT HELP INFO HERE ..."; print "
"; print " "; print "
"; print "
"; endif; print "$_footer"; // // /////////////////////////////////////////////////////////////////////////////// if($action == "install"): $db_server ="localhost"; $db_user ="root"; $db_passwd = ""; $appdbname = "pccstm"; // Change this to your app database name $appuser = "pccstm"; // Change this to your app user name $apppasswd = "pccstm"; // Change this to your app user passwd $cmd_mysql = "/usr/local/bin/mysql"; // Location of mysql client $cmd_par = "-u root"; // We will execute mysql client as $myapp_dmp = "pccstm.sql"; // Replace with your mysqldump $app_index = "index.php"; // Main File to your app ///////////////////////////////////////////////////////////////////////////////// // Connect to Database // You may not need to modify any thing here // function connectDB() { global $db_host, $db_user, $db_passwd; mysql_connect($db_name, $db_user, $db_passwd); } connectDB(); $create_webdb = "CREATE DATABASE $appdbname"; $cwebdb = mysql_query($create_webdb); if (!empty($cwebdb)) { // Do Nothing } else { echo mysql_error()."
\n"; } $create_webuser = "GRANT SELECT,INSERT,UPDATE,DELETE on $appdbname.* TO $appuser@$db_server IDENTIFIED by \"$apppasswd\""; $cwebuser = mysql_query($create_webuser); if (!empty($cwebuser)) { // Do Nothing } else { echo mysql_error()."
\n"; } $create_tbl = "$cmd_mysql $cmd_par $appdbname < $myapp_dmp"; exec($create_tbl); print "$packagename has been installed."; print " Click here to test it out"; print "
Bugs and Comments $coder"; endif; ///////////////////////////////////////////////////////////////////////////// ?> ./pccstm/pccstm.sql100644 0 0 11464 7065222060 12743 0ustar rootwheel# MySQL dump 6.0 # # Host: localhost Database: pccstm #-------------------------------------------------------- # Server version 3.22.26a # # Table structure for table 'account_type' # DROP TABLE IF EXISTS account_type; CREATE TABLE account_type ( acct_typeid int(10) DEFAULT '0' NOT NULL auto_increment, acct_type text, PRIMARY KEY (acct_typeid), KEY acct_typeid_idx (acct_typeid) ); # # Dumping data for table 'account_type' # LOCK TABLES account_type WRITE; INSERT INTO account_type VALUES (1,'Expenses'),(2,'Revenues'),(3,'Liabilities'),(4,'Other'),(5,'Donation'); UNLOCK TABLES; # # Table structure for table 'accounts' # DROP TABLE IF EXISTS accounts; CREATE TABLE accounts ( acct_id int(10) DEFAULT '0' NOT NULL auto_increment, acct_number text NOT NULL, acct_name text NOT NULL, acct_typeid int(10), PRIMARY KEY (acct_id) ); # # Dumping data for table 'accounts' # LOCK TABLES accounts WRITE; INSERT INTO accounts VALUES (1,'610','Meals',1),(2,'620','Transportation',1),(3,'630','Lodging',1),(4,'640','Miscellaneous',1),(5,'410','Rental Income',2),(6,'650','Power Bill',1),(7,'420','Mutual Fund',2),(8,'660','Telephone Service',1),(9,'430','Second Mutual Fund',2),(10,'670','Rent',1); UNLOCK TABLES; # # Table structure for table 'my_addresses' # DROP TABLE IF EXISTS my_addresses; CREATE TABLE my_addresses ( AddressID int(11) DEFAULT '0' NOT NULL auto_increment, FirstName varchar(50), LastName varchar(50), SpouseName varchar(50), Address varchar(255), City varchar(50), StateOrProvince varchar(20), PostalCode varchar(20), Country varchar(50), EmailAddress varchar(50), HomePhone varchar(30), WorkPhone varchar(30), WorkExtension varchar(20), FaxNumber varchar(30), Birthdate datetime, SendCard tinyint(4), Notes mediumtext, PRIMARY KEY (AddressID), KEY AddressID (AddressID) ); # # Dumping data for table 'my_addresses' # LOCK TABLES my_addresses WRITE; INSERT INTO my_addresses VALUES (1,'Nancy','Davolio','Paul','507 - 20th Ave. E.\r\nApt. 2A','Seattle','WA','98122','USA','nancyd@anywhere.com','(504) 555-9857','(504) 555-9922',NULL,'(504) 555-7722','1972-08-04 00:00:00',0,NULL),(2,'Andrew','Fuller','Anne','908 W. Capital Way','Tacoma','WA','98401','USA','andrewf@anywhere.com','(504) 555-9482','(504) 555-9933',NULL,'(504) 555-7733','1961-06-17 00:00:00',0,NULL),(3,'Janet','Leverling','Robert','722 Moss Bay Blvd.','Kirkland','WA','98033','USA','janetl@anywhere.com','(504) 555-3412','(504) 555-9944',NULL,'(504) 555-7744','1965-07-14 00:00:00',1,NULL),(4,'Margaret','Peacock','Michael','4110 Old Redmond Rd.','Redmond','WA','98052','USA',NULL,'(504) 555-8122','(504) 555-9955',NULL,'(504) 555-7755','1965-07-14 00:00:00',1,NULL),(5,'Steven','Buchanan',NULL,'14 Garrett Hill','London',NULL,'SW1 8JR','UK','steveb@anywhere.com','(71) 555-4848','(71) 555-5858',NULL,NULL,'1970-08-12 00:00:00',0,NULL); UNLOCK TABLES; # # Table structure for table 'transactions' # DROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( trans_id int(10) DEFAULT '0' NOT NULL auto_increment, trans_number bigint(8) DEFAULT '0' NOT NULL, trans_date varchar(10), trans_description text, trans_acctid int(10), trans_withdraw decimal(10,2), trans_deposit decimal(10,2), trans_taxable enum('Yes','No'), PRIMARY KEY (trans_id), KEY trans_number_idx (trans_number) ); # # Dumping data for table 'transactions' # LOCK TABLES transactions WRITE; INSERT INTO transactions VALUES (1,2,'1994-06-11','Fancy Meal',1,7.00,0.00,'Yes'),(2,5,'1995-01-02','Hotel',3,35.00,0.00,'No'),(3,8,'1995-04-10','Business Luncheon',1,30.00,0.00,'No'),(4,9,'1994-09-02','Air Fare to Atlanta',2,70.00,0.00,'Yes'),(5,18,'1994-11-20','Dinner',1,20.00,0.00,'No'),(6,22,'1995-06-01','Client Luncheon',1,55.00,0.00,'No'),(7,23,'1995-06-01','Newspapers, Magazines',4,5.00,0.00,'Yes'),(8,25,'1995-02-01','Tip',4,5.00,0.00,'No'),(9,26,'1995-02-01','Hotel',3,60.00,0.00,'Yes'),(10,30,'1995-07-10','Telephone Bill',8,34.00,0.00,'No'),(11,41,'1995-01-01','First Mutual Fund',7,0.00,250.00,'Yes'),(12,43,'1994-07-01','Second Mutual Fund',9,0.00,1000.00,'No'),(13,1,'1995-01-02','Trip to LA',2,5.00,0.00,'No'),(14,1,'1995-01-02','Trip to LA',2,5.00,0.00,'No'),(15,2,'1994-06-11','Fancy Meal',1,7.00,0.00,'Yes'),(16,5,'1995-01-02','Hotel',3,35.00,0.00,'No'),(17,8,'1995-04-10','Business Luncheon',1,30.00,0.00,'No'),(18,9,'1994-09-02','Air Fare to Atlanta',2,70.00,0.00,'Yes'),(19,18,'1994-11-20','Dinner',1,20.00,0.00,'No'),(20,19,'1994-11-20','Cab',2,8.00,0.00,'Yes'),(21,43,'1994-07-01','Second Mutual Fund',9,0.00,1000.00,'No'),(22,0,'0000-00-00','Rent House off of houston',0,0.00,1200.00,'Yes'),(23,0,'0000-00-00','Rent House off of houston',0,0.00,1200.00,'Yes'),(24,0,'2003-11-20','Rent House off of houston',0,0.00,1200.00,'Yes'),(29,1,'2000-10-3','DixesRoad House',1,0.00,50.25,'No'),(30,3,'2000-6-2','Trip to Maine',3,276.23,0.00,'No'); UNLOCK TABLES; ./pccstm/phpbackup.php100644 0 0 2734 7065256152 13410 0ustar rootwheelTranasaction Manager Backup Tool "; if ($argv[0] ==""): print "
"; print "

This Page allows you to make a backup of your database App

"; print "
"; print "BACKUP APPLICATION"; print "
"; endif; $dbname = "pccstm"; //your Application Database Name $_time = date("His"); $_date = date("Ymd"); $dump_filename = "$_date$_time_$dbname.sql"; $_admtool = "/usr/local/bin/mysqldump"; $_gzip = "/bin/tar"; $dump_filename = "$_date$_time_$dbname.sql"; // // Below are the paramaters for mysqldump $par1 = '--opt'; $par2 = '-uroot'; $db_dump = "$_admtool $par1 $par2 $dbname > ./$dump_filename"; $gz_dump = "$_gzip -czf ./$dump_filename.tar.gz ./$dump_filename"; if ($action == "backup"): header("Content-type: application/gzip"); header("Content-Disposition: attachment; filename=$dump_filename.tar.gz"); exec ("$db_dump"); exec ("$gz_dump"); readfile("$dump_filename.tar.gz"); unlink("$dump_filename.tar.gz"); endif; print ""; // ////////////////////////////////////////////////////////////////////////// ?> ./pccstm/reports.php100644 0 0 13056 7065242677 13157 0ustar rootwheelAccount Summary
"; // print "Account Type Summary
"; print "Transaction Summary
"; endif; if($action == "acct_report"): print "

MAIN   |   Account Summary

\n"; $account_summary_qry = "SELECT DISTINCTROW acct_number, acct_name, SUM(transactions.trans_withdraw) AS summary_withdraw, SUM(transactions.trans_deposit) AS summary_deposit FROM accounts LEFT JOIN transactions ON accounts.acct_id = transactions.trans_acctid WHERE transactions.trans_date >= '$date_begin' AND transactions.trans_date <= '$date_end' GROUP BY accounts.acct_number, accounts.acct_name"; $acct_report = mysqlquery($db_name,$account_summary_qry); if(!empty($res)) { } else { echo mysql_error() . "
\n"; } print "

"; print ""; print ""; print ""; print ""; while ($report_acct_data = mysql_fetch_array($acct_report)) { $number = $report_acct_data["acct_number"]; $acctname = $report_acct_data["acct_name"]; $totalwithdrawals = $report_acct_data["summary_withdraw"]; $totaldeposits = $report_acct_data["summary_deposit"]; $balance = $totaldeposits-$totalwithdrawals; printf("",$number,$acctname,$totalwithdrawals,$totaldeposits,$balance); } print "
Account Number Account Name Total Withdrawals Total Deposits Balance
%s%s%s%s%s
"; print "
"; endif; if($action == "accttype_report"): $account_type_qry = "SELECT DISTINCTROW 'account_type.acct_type', accounts.acct_number,accounts.acct_name, SUM(transactions.trans_withdraw) AS summary_withdraw, SUM(transactions.trans_deposit) AS summary_deposit FROM account_type RIGHT JOIN accounts LEFT JOIN transactions ON accounts.acct_id = transactions.trans_acctid, 'account_type.acct_typeid' = accounts.acct_typeid WHERE transactions.trans_date >= '$date_begin' AND transactions.trans_date <= '$date_end' GROUP BY 'account.acct_type', accounts.acct_number, accounts.acct_name"; $acct_type_report = mysqlquery($db_name,$account_type_qry); if(!empty($acct_type_report)) { } else { echo mysql_error() . "
\n"; } print "

MAIN   |   Account Type Summary

"; print "
"; print ""; print ""; print ""; print ""; while ($report_accttype_data = mysql_fetch_array($acct_report)) { $type = $report_accttype_data["account_type.acct_type"]; $number = $report_accttype_data["acct_number"]; $acctname = $report_accttype_data["acct_name"]; $totalwithdrawals = $report_accttype_data["summary_withdraw"]; $totaldeposits = $report_accttype_data["summary_deposit"]; $balance = $totaldeposits-$totalwithdrawals; printf("",$number,$acctname,$totalwithdrawals,$totaldeposits,$balance); } print "
Account Type Account Number Account Name Total Withdrawals Total Deposits Balance
%s%s%s%s%s
"; print "
"; endif; if($action == "trans_report"): $transaction_report = "SELECT DISTINCTROW transactions.*,acct_name FROM accounts LEFT JOIN transactions ON accounts.acct_id = transactions.trans_acctid WHERE transactions.trans_date >= '$date_begin' AND transactions.trans_date <= '$date_end' AND accounts.acct_id = transactions.trans_acctid"; $transactionreport = mysqlquery($db_name,$transaction_report); if(!empty($transactionreport)) { } else { echo mysql_error() . "
\n"; } print "

MAIN   |   Transaction Summary

"; print "
"; print ""; print ""; print ""; print ""; while ($report_trans_data = mysql_fetch_array($transactionreport)) { $acctname = $report_trans_data["acct_name"]; $transdate = $report_trans_data["trans_date"]; $description = $report_trans_data["trans_description"]; $totalwithdrawals = $report_trans_data["trans_withdraw"]; $totaldeposits = $report_trans_data["trans_deposit"]; $balance = $totaldeposits-$totalwithdrawals; printf("",$acctname,$transdate,$description,$totalwithdrawals,$totaldeposits,$balance); } print "
Account Name Date Description Total Withdrawals Total Deposits Balance
%s%s%s%s%s%s
"; print "
"; endif; ?> ./pccstm/setup_accounts.php100644 0 0 5366 7065250744 14477 0ustar rootwheelYou must Enter an account name and account number ... "; else: $acct_type = strtoupper($acct_type); $query = "INSERT INTO accounts VALUES($acct_id,'$acct_number','$acct_name','$acct_typeid')"; $res = mysqlquery("$db_name",$query); if(!empty($res)) { echo ""; } else { echo mysql_error() . "
\n"; } endif; endif; if($argv[0] == ""): print "

MAIN   |   Create / Manage Accounts

\n"; print "
"; print "
Account Name Account Number Account Type
"; $query = " SELECT accounts.*,account_type.* FROM accounts,account_type WHERE accounts.acct_typeid = account_type.acct_typeid AND accounts.acct_typeid = account_type.acct_typeid ORDER BY acct_number ASC LIMIT $start,$limit"; print "
"; $account_select = mysqlquery($db_name,$query); if(!empty($account_select)) { while ($select_account = mysql_fetch_array($account_select)) { $num = $select_account["acct_number"]; $name = $select_account["acct_name"]; $type = $select_account["acct_type"]; $history = "History"; printf("\n",$num,$name,$type); } } else { echo mysql_error() . "
\n"; } print "
Number Name Type
%s %s %s
"; endif; print "$_footer"; ?> ./pccstm/setup_acct_type.php100644 0 0 3312 7065220540 14607 0ustar rootwheel"; } else { echo mysql_error() . "
\n"; } endif; if($argv[0] == ""): print "

MAIN   |   Create / Manage Accounts Types

\n"; print "
"; print "

Enter a New Account Type

Account Type
"; print "
"; $query = "SELECT * from account_type order by acct_type"; $res = mysqlquery($db_name,$query); while($acct = mysql_fetch_array($res)) { printf ("", $acct["acct_type"]); } print "
Current Account Type(s)
%s
"; endif; print "$_footer"; ?>