mogaka Light Poster

Stored Procedure
    -- Procedure name: billing_to_invoice_update
    BEGIN
    UPDATE accounts.0_debtor_trans SET ov_amount=ov_amount+fee-oldfee WHERE trans_no=encounter AND branch_code=pid;
    UPDATE accounts.0_debtor_trans_details SET unit_price=fee/units,quantity=units,qty_done=units WHERE debtor_trans_no=encounter AND stock_id=CONCAT(code_type,'/',code);
    -- gl updates
    UPDATE accounts.0_gl_trans SET amount=ROUND((-1)*fee) WHERE account=code_type AND memo_=CONCAT(code_type,'/',code) AND last_service_encounter=encounter;
    UPDATE accounts.0_gl_trans SET amount=ROUND(fee) WHERE account=1100 AND memo_=CONCAT(code_type,'/',code) AND last_service_encounter=encounter;
    END
    BEGIN
Trigger That Calls The above code
    BEGIN
    CALL billing_to_invoice_update(OLD.pid,OLD.encounter,
    NEW.units,NEW.fee,OLD.fee,OLD.code_type,OLD.code);
    END

The above scripts shows a trigger and a stored procedure named : billing_to_invoice_update. The purpose is to update changes on one table in another table.data to be updated may be a large array of 10 or more rows , but when the rows to be affected is more than one,then the stored procedure repeats the updates on the other table-done by stored procedure. Can somebody help me ensure that each row updates only one row in the corresponding table.

mogaka Light Poster

CASE WHEN ((SELECT COUNT(id) FROM users) > 0) THEN
    BEGIN
       INSERT INTO users(username,password) VALUES('Moshe','Ahuva');
   END
ELSE 
    BEGIN
       INSERT INTO users(username,password) VALUES('Zvi','Idan');
    END
END

My aim is to save with a condition. but it fails and says: "Check the manual that correponds to the right syntax near ELSE BEGIN ....". please re-write it better.

mogaka Light Poster

SELECT code_type,code,encounter,pid,provider_id,date,CONCAT('code_tye',':','code') INTO @code_type,@code,@encounter,@pid,@povider_id,@date,@standard_code FROM billing
FOR EACH ROW
BEGIN
SELECT procedure_type_id INTO @type_id FROM procedure_type WHERE standard_code='@standard_code'
FOR EACH ROW
BEGIN
INSERT INTO procedure_order(procedure_type_id,date_ordered,provider_id,date_collected,order_priority,order_status
,patient_instructions,patient_id,encounter_id) VALUES('@type_id','@date','@provider_id','@date','high','pending','complaints','@pid','@encounter')
END
END
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EACH ROW BEGIN SELECT procedure_type_id INTO @type_id FROM procedure_type WHERE ' at line 2

what is the problem ?

mogaka Light Poster

$res=mysql_query("SELECT code_type,code,date,provider_id FROM billing WHERE encounter='$encounter' AND pid='$pid'");
while($row=mysql_fetch_array($res)){
$code_type=$row['code_type'];
$code=$row['code'];
$date=$row['date'];
$provider=$row['provider_id'];
$standard_code=$code_type.":".$code;
$res1=mysql_query("SELECT procedure_type_id FROM procedure_type WHERE standard_code='$standard_code'");
while($row1=mysql_fetch_array($res1)){
$procedure_type_id=$row1['procedure_type_id'];
mysql_query("INSERT INTO procedure_order(procedure_type_id,date_ordered,provider_id,date_collected,order_priority,order_status
,patient_instructions,patient_id,encounter_id) VALUES('$procedure_type_id','$date','$provider','$date','high','pending','$complaints','$pid','$encounter_id')")
or die(mysql_error());
}
}

The above query saves(inserts) more than once in the database. what can be the cause especially in the while loops ? please help me rectify.

mogaka

$params = new xmlrpcmsg('users_add',
                         array(new xmlrpcval('firstname', 'string'),
                               new xmlrpcval('middlename', 'string'),
                               new xmlrpcval('lastname', 'string')));

the source of $params is s shown above. note that values i have put are direct strings,I real scenarion,they can be like $firstname.

mogaka Light Poster

function addUsers($params) {
      $fnameval = $params->getParam(0);
    $fname = $fnameval->scalarval();
    $mnameval = $params->getParam(1);
    $mname = $mnameval->scalarval();
    $lnameval = $params->getParam(2);
    $lname = $lnameval->scalarval();
    if(mysql_query("INSERT INTO users(fname,mname,lname) VALUES('$fname','$mname','$lname')")){
     $mess="data and synced successfully";}
     else{
     $mess="data saved but not synced as required";}
    $response = array('response' => new xmlrpcval($mess, 'string'));
    return new xmlrpcresp(new xmlrpcval($response, 'struct'));
}

Look at the above code. I intended to implement XML-RPC(xml remote procedure call) using php. the problem is that it takes time to parse a variable(see peocedure of getting $fname). is there a shorter way i can get the vairables directly e.g $fname=$params['fname'] without getting index then scalarval() ? help.
thanks.

mogaka Light Poster

I have data that is inserted into a table A. I want that before 'insert' to table A, I should capture Specific data from the array and insert it into table B. Can somebody show me how to do that ?

mogaka Light Poster

update drugs,drug_sales SET drugs.b_price=(SUM(drug_sales.fee)/SUM(drug_sales.quantity)) WHERE drugs.drug_id=drug_sales.drug_id AND drug_sales.pid=0

update drugs,drug_sales SET drugs.b_price=SUM(drug_sales.fee)/SUM(drug_sales.quantity) in drugs.drug_id=drug_sales.drug_id AND drug_sales.pid=0

The intention of the above 2 queries is to update a table called drugs based on computational results of table called drug_sales. The two queries fails and the error is: "#1111 - Invalid use of group function".

Please help me solve/debug the query. am using mysql DB.

mogaka

I mean this: suppose you fetch and get for instance a=NO,b=NO,c=YES,d=NO,e=YES,f=YES,g=NO,h=YES. filter the array picking out variables with value YES from the arry. List them and list those variables with value NO. for the above query, I wanted a query that can list clolumns of a particular row with value YES. I lsit them and list others later.

mogaka Light Poster

I have a list of columns of table that holds boolean values. consider a,b,c,d,e,f,g,h as column names with YES or NO values. I want to query and list column names whose value is YES. can some write a mysql query that can list the columns. if a column has an underscore i want to replace underscore with space or " ".can somebody write the required script using php.

Thanks for help.

mogaka

Your code is god but still only lists one row,sums up everything and stops there. note that i wanted to for instance encounter_id 25 in payments table appears only once. but on billing table, it appears 6 times since the person received 6 services. each service has a fee so i want to get the total cost for encounter 25 obtained from payments table so that i can know how much you paid,cost of services(in billing table and computed from cost of each of six services) so as to get balance.

mogaka Light Poster

$query = "SELECT encounter,amount1,amount2,posted1,posted2 FROM payments WHERE pid='$patient_id'";
   $res = mysql_query($query);
$max =25;
$num_rows=mysql_num_rows($res);
$x_count=0;
   while($row=sqlFetchArray($res)){
    $encounter_id=$row['encounter'];
        //$method=$row['method'];   
        $amount1=floatval($row['amount1']);
        $amount2=floatval($row['amount2']);
        $posted1=floatval($row['posted1']);
       $posted2=floatval($row['posted2']);
       $total_amount_paid=floatval($amount1+$amount2+$posted1+$posted2);
      for($x_count=0;$x_count<$num_rows;$x_count++){
       //$encounter_id[$x_count]=$encounter_id;
     $query = "SELECT date,SUM(fee) AS totalcharges FROM billing WHERE pid='$patient_id' AND encounter=$encounter_id";
   $res = mysql_query($query);
     while($row=sqlFetchArray($res)){
       $date_fee=$row['date'];
       $total_charges=floatval($row['totalcharges']);
        $balance=floatval($total_charges-$total_amount_paid);

 $pdf->SetFillColor(254,254,254);
$pdf->Ln();
$pdf->SetX(10);
                $pdf->Cell(50,4,$date_fee,1,0,'L',1);
        $pdf->Cell(40,4,$encounter_id,1,0,'L',1);
        $pdf->Cell(40,4,$total_charges,1,0,'L',1);
                $pdf->Cell(40,4,$total_amount_paid,1,0,'L',1);
                 $pdf->Cell(40,4,$balance,1,0,'L',1);}
}
}

The above code shows my attempt to fetch key called encounter from table called payments. for each encounter, i want to obtain records(many) from another table called billing. i want to sum some values based on an encounter.
the above code returns only one row and i have over 6 rows that i expect to see. where is the problem ? can somebody help me ?

mogaka Light Poster

 <?php echo "<center><a href='report.php?date_range=$date_range&patient_id=$patient_id&proce_name=$proc_name>
<img src='pdf.jpg' alt='Print Lab Resutl' width='32' height='32'>Print Report</a><center>
 </p></center>";?>

I want to submit some the above parameters to the server side script for processing. However, I do dont receive the sent parameters on the server script. can somebody identify and rectify where the error is .I want to retrieve the variables by $_GET method

mogaka

Ok. I have different systems running on mysql,sql server 2005,postgresql and oracle. what i need is just a DATABASE SYNCRONIZATION AND REPLICATION SYSTEM THAT CAN:

  1. Replicate and synchronize all mysql databases across sites i.e branches to our cloud centre.

  2. Replicate and syncronize all postgres databases across sites i.e branches to our cloud/master database

3 Replicate and synchronize all oracle databases across sites i.e branches to our cloud/master database

In other words, this is not necessarly a database management system but a software that is generic in that the user can graphically connect to two remote and same databases like mysql<=>mysql database and synchronize their data. one is the source and the other is the destination.

Or take example of the banking industry databases. They are spread across their branches but customer data is accessible from any location meaning that local/branch data is replicated at given interval to the main database. that is basically what i want;Only that the system should have capability of synching a variety of Database Management systems.

mogaka Light Poster

I am looking for a universal system that can replicate/Synronize database schemas and changed data for mysql,sql server,Oracle or atleast mysql and postgresql.

Our company has different branches each with its own local data but any changes in a local database is supposed to be replicated to the central database. The system should also be able to synhronize changes in database schema.

Syncronization is two-way i.e the site database should completely sync its data with central database and also receive changes in the central database to local storage. The reason for two-way is to ensure that client data is accessible from any of our branches and can therefore transact from any branch.

mogaka Light Poster

First time click:

Screenshot-110

Second Round Mouse click:

Screenshot11

The above images shows screen short of a popup that is shown by clicking on a link from a html table i created using Jquery DataTables plugin and data from mysql database. in essense, when i click the linkable column of a table for the first time, it shows the popup but under the main table.closing the popup and click on the same link again now displays the popup clearly IF I DONT REFRESH THE browser. restarting machine or firefox doesnt help. below is part of css code:

  <link href="css/dropdown/dropdown.css" media="screen" rel="stylesheet" type="text/css" />
<link href="css/dropdown/themes/default/default.ultimate.css" media="screen" rel="stylesheet" type="text/css" />

        <style type="text/css">
            @import "media/css/demo_table_jui.css";
            @import "media/themes/smoothness/jquery-ui-1.8.4.custom.css";

        </style>
 <script type="text/javascript" charset="utf-8">
            $(document).ready(function(){
                $('#custlist').dataTable({
                    "sPaginationType":"full_numbers",
                    "aaSorting":[[2, "desc"]],
                     "iDisplayLength":5,
                    "bJQueryUI":true
                });
            })

        </script>

  <table width="100%" border="0" cellpadding="0" class="body" cellspacing="0" align="center">
  <tr>
    <td align="center" valign="top">
      <table width="100%" border="0" cellspacing="0" cellpadding="0" class="body">
        <tr>
          <td valign="top" align="center" width="35%">
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td>
                  <table width="70%" border="0" cellspacing="0" cellpadding="0" class="table_background">
                      <tr>
                        <td>
<!-- gap between tables>-->
                          <table width="70%" border="0" cellspacing="1" cellpadding="0">
                            <tr valign="top">
                              <td width="70%" class="table_heading">
                                <div align="center">
                                Customers List
                                </div>
                              </td>

                            </tr>
                            <tr valign="top">
                              <td width="65%" class="row1">
                                <table width="100%" border="0" cellspacing="5" cellpadding="1" class="row1">
                                  <tr>
                                    <td width="70%">

                                <div></div><div id="grid">
                    <table id="custlist" class="display">
                <thead>
                    <tr>
                        <th>Customer Name</th>
                        <th>Category</th>
                        <th>Contact</th>
                        <th>email</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    while ($row = mysql_fetch_array($result)) {
                        ?>
                        <tr>
                            <td><?= "<a href=? onclick=\"xajax_getContact('".$row['mobile']."','".$row['id']."');return false;\">".$row['customer']." (".$row['mobile'].")</a>"?></td>
                            <td><?=$row['account_type']?></td>
                            <td><?=$row['mobile']?></td>
                            <td><?=$row['email']?></td>

                        </tr>
                        <?php
                    }
                    ?>
                </tbody>
            </table>
        </div>

    So what can be the cause ?

mogaka Light Poster

Consider the following code:

while($row=mysql_fetch_assoc($result)){
                echo
                '<tr> ';
                echo    '<td><a href="#?id='.$row['id'].'" class="dep_link"><img src="images/icn_edit.png" title="Edit"></a>&nbsp;<a href="#"><img src="images/icn_trash.png" title="Trash"></a></td>';

In my php form, i have created a jquery dialog link such that when the user clicks the 'icn_edit.png' image , a new iframe is loaded with fields for update. i want to pass the 'id' variable from the link of that particular html table row to the division of html where the iframe is created so that i can use it as key for querying database. apparently, the iframe loads but the 'id' is not displayed in the test textfield.

how can i post the variable/value ie. the 'id'. the code works when i replace the '#' with an external file but not when i intend to pass it to a html div.

mogaka Light Poster

<?php
require_once('mail.inc.php');
$con = mysql_connect("localhost","root","12345");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("billtest",$con);
$today=strtotime(date("Y-m-d"));
$query1="SELECT * FROM ab_transactions_paypal WHERE status=0 AND expiry_date>$today";
$result=mysql_query($query1);
if(mysql_num_rows($result)>0){
$count=mysql_num_rows($result);
for($j=0;$j<$count;$j++){
$row=mysql_fetch_array($result);
$token_id=$row['token_id'];
$account_id=$row['account_id'];
$sku=$row["sku"];
$inv_id=$row['invoice_id'];
$bill_amt=$row['amount'];
$cust_email=$row['cust_email'];
$url="https://ssl.3gsecure.net/api/payment/checktoken.asp?TransactionToken=$token_id";
$httpRequest_OBJ = new httpRequest($url, HTTP_METH_POST);
$result = $httpRequest_OBJ->send();
$mine=$httpRequest_OBJ->getResponseBody();
echo $httpRequest_OBJ->getResponseBody();
$s = explode(";",$mine);
foreach($s as $k){
 $v=explode("=",$k);
  $a[$v[0]]=$v[1];
}
//print_r($a);
echo "<br />";
//echo $a['TransactionStatus'];
echo "<br />";
if(($a['TransactionStatus'])==000){
$q_update = "UPDATE ab_transactions_paypal SET status=1 WHERE invoice_id=$inv_id AND account_id=$account_id AND sku='$sku'";
mysql_query($q_update) ;

$sql = "UPDATE ab_invoice SET billed_amt=$bill_amt,billing_status=1,process_status=1 WHERE id=$inv_id AND account_id=$account_id AND sku='$sku'";
mysql_query($sql) ;
#begin service recharge##
$query9="SELECT * FROM ab_service WHERE  account_id=$account_id AND sku='$sku'";
//echo $skus;
//mysql_query($query);
$result9=mysql_query($query9);
$row9=mysql_fetch_array($result9);
$cycle=$row9['recur_type'];
$curr_balance=$row9['usage_balance'];
$mcf=$row9['mcf'];
$price=$row9['price'];
$amt=$bill_amt;
switch($cycle){
                   //DO STUFF   
                      } 

if($amt<$price){
 //DO STUFF
}
else{
//SOME CODE HERE

if ($account_type=="CORPORATE"){

//do stuff A
}
else{
//do stuff B
}
if($account_type=="CORPORATE"){
//dO stuff C
}
else{
//dO stuff D
}
##initialize sendmails class-by creating an object from it##
$sendmail=new sendmails();
##now send the email##
$html=$message;
$sendmail->authSendEmail($from,$to,$subject, $html) ;

} 
##end service recharge ##                                        
}
else{
echo "Payment For The Specified Client Has not been effected. Please wait...";
}
}}
else{
echo "no client";
}
?>

The above code picks all rows that meet criteria from the database. however,from the list of rows in array, it transacts for only one row i.e does not move next and repeat doing specified stuff until it exhausts the array. how can i overcome this ? can somebody re-write it so that it can loop through ...

mogaka

No. items are created on the table. then u read and send them to a php file on the server. in other words, given a large, table (with data), how will you read the data as array and post it to php from html front end ?

mogaka Light Poster

I want to iterate through html table and display the values. for instance, column keys are fname,lname,mkt,positions. if for instance there are 10 rows,how can i access them and send them to php script ?

mogaka Light Poster

function name_loop(){
    var names_array = [];
    $('.name_class').each(function(index){
        names_array[index] = $.trim($(this).val());
    });
    return names_array;
}

the problem with the above code is that it returns only the first element and not the specified array. can someone solve it for me

mogaka Light Poster

consider code below:

//Consider the php part(process.php)

<table border="1">
<thead>
    <tr>
        <th>Name</th>
        <th>Language</th>
    </tr>
</thead>
<tbody>
    <?php foreach($_POST['names'] as $k=>$v){ ?>
    <tr>
        <td><?php echo $v; ?></td>
        <td><?php echo $_POST['languages'][$k]; ?></td>
    </tr>
    <?php } ?>
</tbody>
</table>

//html part``

<HTML>
<HEAD>
    <TITLE> Add/Remove dynamic rows in HTML table </TITLE>
<script type="text/javascript" src="jquery-1.6.2.min.js"></script>
    <script language="javascript">
function name_loop(){
    var names_array = [];
    $('.name_class').each(function(index){
        names_array[index] = $.trim($(this).val());
    });
    return names_array;
}
        function addRow(tableID) {

            var table = document.getElementById(tableID);

            var rowCount = table.rows.length;
            var row = table.insertRow(rowCount);

            var cell1 = row.insertCell(0);
            var element1 = document.createElement("input");
            element1.type = "checkbox";
            cell1.appendChild(element1);

            var cell2 = row.insertCell(1);
            cell2.innerHTML = rowCount + 1;

            var cell3 = row.insertCell(2);
            var element2 = document.createElement("input");
            element2.type = "text";
            cell3.appendChild(element2);

        }

        function deleteRow(tableID) {
            try {
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;

            for(var i=0; i<rowCount; i++) {
                var row = table.rows[i];
                var chkbox = row.cells[0].childNodes[0];
                if(null != chkbox && true == chkbox.checked) {
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }

            }
            }catch(e) {
                alert(e);
            }
        }

    </SCRIPT>
</HEAD>
<BODY>
 <!--<form action="process.php" method="post">-->
    <INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />

    <INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />

    <TABLE id="dataTable" width="350px" border="1">
        <TR>
            <TD><INPUT type="checkbox" name="chk"/></TD>
            <TD> 1 </TD>
            <TD> <INPUT type="text" class="name_class" name="txt" /> </TD>
        </TR>
<input type="submit" name="btn_submit" id="btn_submit" value="check"  onclick="send_off();"/>
    </TABLE>
 <!--</form>-->
<div id="result"></div>
<script type="text/javascript">
function send_off(){
    var names = name_loop(); //stores the array of names
    //var languages = lang_loop();  //stores the array of languages

    $('#result').load('process.php', {'names' : names}); //submit to php script
    $('input[type=text]').val(''); //empty all textbox
}
</script>
</BODY>
</HTML>

the problem is thea it ...

mogaka Light Poster

i have the following query:

1.$query="SELECT * FROM ab_service WHERE account_id=$account_id AND sku='".$sku."'";

2.$query="SELECT * FROM ab_service WHERE account_id=$account_id AND sku='$sku'";

none of the above seems to work.the problem is with $sku var because when i dont put it as the condition, i get results.what is the problem ? the variable $sku is already asigned from another table.

mogaka Light Poster

how can i submit form values to server without refreshing page i.e using ajax.consider fname,lnm,gnd,dpk as sample form fields to illustrate this.

mogaka Light Poster

I am trying to process an array to get variable name and its value. below is the code.

$s = explode("&",$mine);
foreach($s as $k=>$v){
 $v=explode("=",$v);
for($i=0;$i<count($v);$i++){
  $a[$v[$i]]=$v[$i+1];
}
print_r($a);
}

The prolem is that the array repeats itself in output. as shown(look at it keenly):

Array ( [APIresult] => 000;APIresultexplnation [000;APIresultexplnation] => Transaction Created;TransactionToken [Transaction Created;TransactionToken] => 79A9DC2A-622B-4334-B610-001F4976C6C0 [79A9DC2A-622B-4334-B610-001F4976C6C0] => )

I want it give variable and value e.g [APIresult] => 000;TransactionToken => 79A9DC2A-622B-4334-B610-001F4976C6C0. The response i.e original string is this:

APIresult=000;APIresultexplnation=Transaction Created;TransactionToken=D2AB45E4-1DE7-4828-96CA-3A884697109A

This is a response from a third party API. How can i get round this because i want to get the result for each variable for easy processing.

mogaka Light Poster

i have created a php application where am using a barcode scanner to load item name and prices from database. the problem is that once the barcode reader finishes reading the barcode, the details are loaded and it tabs to the next field. this means that the user has to click again on the barcode field to ficus it. how do i prevent the cursor from focusing the next field after scanning i.e focus again on the barcode field after scanning to allow automatic scanning of new item ?

mogaka Light Poster

i have created a php application where am using a barcode scanner to load item name and prices from database. the problem is that once the barcode reader finishes reading the barcode, the details are loaded and it tabs to the next field. this means that the user has to click again on the barcode field to ficus it. how do i prevent the cursor from focusing the next field after scanning i.e focus again and again on the barcode field only for faster selling of items ?

mogaka Light Poster

i can create an asp.net application but i don't know how to compile it to run. can somebody help me on how to make it run because at the moment, it runs on visual studio alone. how can i make it run in an environment without vb.net IDE?

mogaka Light Poster

hi you all, I am creating an application which requres generation of random keys cosisting of 4 numbers(0-9). However, i realized that at some point,the random numbers repeats themselves. how can i avoid this repetition.the numbers form the primary key of a column in sql database table.

mogaka

no i have solved this myself. i now want to know how i can set my application run continuously update its database say after 30 minutes according to my defined values for updates.