We are using MySQL and Perl to program our website, we have it all done, but now we are working on the payroll, when we send out checks using our Automatic Data Processing company, we get an excel list of all the checks (the account identifier, the check number and the amount of the check), we then have to go update the database to change the record from preissued to issued, the check number and timestamp.

The problem is that if the employee had 3 or more checks (they are combined into one check by the payroll processor), I cannot find the record that shows preissued because I have to do a search this way: 
my $_checkNo = 0;
my $_checkAmt = 1;
my $_emp_id = 2;
my $_paydate = 3;
my @fileparts = split(/\,/, $_line);

# Get potid from pot(Pay Out Tracking) table
my $_potid = $dbh->selectrow_array(qq{SELECT `potid` FROM `pot` WHERE 
+`status` = "preissue" AND `amount` = ? AND `adpid` = ?}, undef, $file
+parts[$_checkAmt], $fileparts[$_emp_id]);
if(!$_potid) {
    # ok, the amount does not match any, so I'm sure they had more tha
+n one transaction.... Check for two:
    ($_potid,$_chkamt) = $dbh->selectrow_array(qq{SELECT `potid`,`amou
+nt` FROM `pot` WHERE `status` = "preissue" AND `adpid` = ?}, undef, $
+fileparts[$_emp_id]);
    if($_potid) {
        $_newChkAmt = sprintf('%.2f', $fileparts[$_checkAmt] - $_chkam
+t);
        $_potid2 = $dbh->selectrow_array(qq{SELECT `potid` FROM `pot` 
+WHERE `status` = "preissue" AND `amount` = ? AND `adpid` = ?}, undef,
+ $_newChkAmt, $fileparts[$_emp_id]);
    }
    if($_potid && $_potid2) {
        $inserted = $dbh->do(qq{UPDATE `pot` SET `status` = "issued", 
+`checkno` = ?, `statuschanged` = ? WHERE (`potid` = ? OR `potid` = ?)
+}, undef, $fileparts[$_checkNo], $fileparts[$_paydate], $_potid, $_po
+tid2);
    }
    if($inserted) {
        $_success++;
    } else {
        $_failed++;
    }
}

[download]
That will pick up only one or two... I just wrote that code to show you what I'm doing, but there is a lot more code, that is not the complete code or the clean version of it so forgive my errors that I put... the code works now, but it excludes paychecks that have 3 or more checks combined into one because the amounts won't match.

So my question is this, is there a way with Perl/MySQL to do something like this and make it work: 
$dbh->do(qq{UPDATE `pot` SET `status` = "issued", `checkno` = ?, `stat
+uschanged` = ? WHERE `status` = "preissue" AND `apdid` = ? AND $filep
+arts[$_checkAmt] IN SUM(amount)}, undef, @placeholder);
# @placeholder will contain all the values for the ?....

[download]
I see the logic in my head but cannot figure out a way to get it to work... what I want it to do is get MySQL to update all the records that have that employee identifier and are matched and the amounts all equal the amount I am giving it.

Please let me know if you don't understand what I'm trying to do, I've been up for about 22 hours programming and am totally exhausted so not thinking 100% clearly.

Thanks for any advice you can offer.
thx,
Richard

In reply to payroll, mysql and perl question by powerhouse

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.