$icons{cal} = "
";
$icons{m} = "
";
$icons{i} = "
";
$icons{corn} = "
";
$icons{thick} = "
";
$icons{w} = "
";
$icons{unlock} = "
";
$icons{subrecipe} = "
";
$icons{mainrecipe} = "
";
our %table; #/define table styles
$table{start} = qq ~ |
|
";
#~ print "--- suc: ".${$leap->{ret}}->{suc};
#~ print "--- kf: ".${$leap->{ret}}->{suc};
#~ print "";
my $tm = localtime(time);
my $datetime = sprintf("%02d-%02d-%04d %02d:%02d:%02d",$tm->mday,($tm->mon+1),($tm->year + 1900), $tm->hour, $tm->min, $tm->sec);
my $date = sprintf("%02d-%02d-%04d",$tm->mday,($tm->mon+1),($tm->year + 1900));
my %today;
($today{day}, $today{month}, $today{year}) = ($tm->mday,($tm->mon+1),($tm->year + 1900));
if ($R::action eq "logche") {
#verify user and password quickly
my $dbh = DBI->connect("DBI:mysql:;host=$dbhost",$dbuser,$dbpassword) or die $DBI::errstr;
my $results = $dbh->prepare("
select * from spaceusers.users where username=? and password=? and active = 1 and type != 3;
;") or die $dbh->errstr();
$results->execute($R::lg_name, $R::lg_password) or die $results->errstr();
my $ref = $results->fetchrow_hashref();
if ( $ref->{username} ) {
print "welcome back";
} else {
if (! $R::lg_password) {
print "password please";
} else {
print "password still not full";
}
}
}
elsif ($R::intransfer eq 'RETREIVEIBTS') {
my $sent = eval($R::query);
my $send_back;
#~ open (debugfile,">/deb.deb");#~ print debugfile Dumper($sent);#~ close debugfile;
my $error;
my $dbh = DBI->connect("DBI:mysql:;host=$dbhost",$dbuser,$dbpassword) or die $DBI::errstr;
#test for existing database;
my $dbflag = 1;
my $results = $dbh->prepare("select * from $sent->{db}.suppliers;;") or die $dbh->errstr();
$results->execute() or $dbflag = -1;
if ($dbflag <= 0) {
$error = "ERROR - no such database here.";
}
if (! $error) {
#find branch
$results = $dbh->prepare("select * from $sent->{db}.suppliers where code=?;;") or die $dbh->errstr();
$results->execute($sent->{mycode}) or die $results->errstr();
my $ref = $results->fetchrow_hashref();
my $supplier_id = $ref->{id};
if (! $ref->{id}) {
$error = "ERROR - no such branch here.";
}
if (! $error) {
#retreive IBT details...
$results = $dbh->prepare("select branch_documents.id, purchases.qty, price, stockitems.sku code
from $sent->{db}.purchases, $sent->{db}.stockitems, $sent->{db}.branch_documents
where
purchases.document_id = branch_documents.id
and purchases.stockitems_id = stockitems.id
and branch_documents.branch_id = ? and branch_documents.document_type = 7
and branch_documents.current_stage = 2;") or die $dbh->errstr();
$results->execute($supplier_id) or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
push @{$send_back->{ibts}->{$ref->{id}}}, [$ref->{qty},$ref->{price},$ref->{code}];
}
}
}
$send_back->{error} = $error;
print Dumper($send_back);
}
elsif ($R::intransfer eq 'RETREIVEIBTS_SETDONE') {
my $dbh = DBI->connect("DBI:mysql:;host=$dbhost",$dbuser,$dbpassword) or die $DBI::errstr;
my $results = $dbh->prepare("update $R::remotedb.branch_documents set current_stage = 3
where
id = ?;
;") or die $dbh->errstr();
$results->execute($R::query) or die $results->errstr();
print "done";
}
elsif ($R::intransfer eq 'INCOMINGIBT') {
my $sent = eval($R::query);
my $send_back;
#open (debugfile,">/deb.deb"); #print debugfile Dumper($sent); #close debugfile;
logit(Dumper($sent));
my $error;
my $dbh = DBI->connect("DBI:mysql:;host=$dbhost",$dbuser,$dbpassword) or die $DBI::errstr;
my $results;
#test for existing database;
my $dbflag = 1;
$results = $dbh->prepare("select * from $sent->{db}.suppliers;;") or die $dbh->errstr();
$results->execute() or $dbflag = -1;
if ($dbflag <= 0) {
$error = "ERROR - no such database here.";
}
if (! $error) {
#find branch
$results = $dbh->prepare("select * from $sent->{db}.suppliers where code=?") or die $dbh->errstr();
$results->execute($sent->{mycode}) or die $results->errstr();
my $ref = $results->fetchrow_hashref();
my $supplier_id = $ref->{id};
if (! $ref->{id}) {
$error = "ERROR - no such branch here.";
}
if (! $error) {
$results = $dbh->prepare("select * from $sent->{db}.branch_documents where uid=?;;") or die $dbh->errstr();
$results->execute( $sent->{uid} ) or die $results->errstr();
my $ref = $results->fetchrow_hashref();
if ($ref->{id}) {
$error = "ERROR - Document already exists.";
}
if (! $error) {
my %ids;
foreach my $ibt (@{$sent->{ibt}}) {
$results = $dbh->prepare("select * from $sent->{db}.stockitems where sku=?;;") or die $dbh->errstr();
$results->execute( @{$ibt}[2] ) or die $results->errstr();
my $ref = $results->fetchrow_hashref();
if (! $ref->{id}) {
$error .= "ERROR @{$ibt}[2] item not found."; } else { $ids{@{$ibt}[2]} = $ref->{id}; } } if (! $error) { # everything is fine - do the IBT - IN $results = $dbh->prepare("select * from $sent->{db}.numbers where forcase = 9;;") or die $dbh->errstr(); $results->execute( ) or die $results->errstr(); my $ref = $results->fetchrow_hashref(); my $number = $ref->{number}; my $new_number = $number + 1; $results = $dbh->prepare("insert into $sent->{db}.branch_documents (document_type, branch_id, seq_nr, stamp, current_stage, uid) values (9, ?, ?, current_timestamp, 2, ?) ;") or die $dbh->errstr(); $results->execute( $supplier_id,$new_number,$sent->{uid}) or die $results->errstr(); $results = $dbh->prepare("SELECT LAST_INSERT_ID() lastid FROM $sent->{db}.branch_documents limit 1;"); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $lastid = $ref->{lastid}; if ($number) { $results = $dbh->prepare("update $sent->{db}.numbers set number = ? where forcase = 9;"); $results->execute($new_number) or die $results->errstr(); } else { $results = $dbh->prepare("insert into $sent->{db}.numbers (number,forcase) values (?,9);"); $results->execute($new_number) or die $results->errstr(); } foreach my $ibt (@{$sent->{ibt}}) { my $code = @{$ibt}[2]; my $qty = @{$ibt}[0]; my $price = @{$ibt}[1]; $results = $dbh->prepare("insert into $sent->{db}.branch_documents_details (document_id,stockitem_id,price,qty) values (?,?,?,?) ;"); $results->execute($lastid,$ids{$code},$price,$qty) or die $results->errstr(); } $send_back->{success} = "Document was successfuly transfered."; } } } } $send_back->{error} = $error; print Dumper($send_back); } elsif ($R::intransfer eq 'IBTRECEIVE') { # a_tests.html #decode -> encode -> send back my $sent = eval($R::query); $sent->{"Hi There"} = "This is processed data."; print Dumper($sent); } elsif ($R::intransfer eq 'IBTSEND') { print "Hello world!"; } elsif ( $session->param("~logged-in") ) { #start normal operation #initialize my (%shop, %shopname, $template, $formhtml, %formvars, $mess); my $profile = $session->param("~profile"); my $username = $profile->{username}; my $usertype = $profile->{type}; my $database = $profile->{database}; my $sharedclients = $profile->{sharedclientsdatabase}; my $sharedclients_shopid = $profile->{sharedclientsshopid}; my $userid = $profile->{userid}; #LET's load settings from database to overwrite sto files my $settings_pm = $session->param("~settings_pm"); my $log; foreach (split(/:/,$settings_pm)){ my($k,$v) = split(/=/); $sys_settings{$k} = $v; } #reset $currency = $sys_settings{'currency'}; $show_noqty_stock = $sys_settings{'show_noqty_stock'} ; my $dprefix = $sys_settings{'dprefix'}; %documents = (1 => ["Tax Invoice",$dprefix."INV"], 2 => ["Proforma Invoice",$dprefix."PRO"], 3 => ["Credit Note",$dprefix."CRN"], 4 => ["Purchase Order",$dprefix."POR"], 5 => ["Supplier Invoice", ""], 6 => ["Supplier Credit Note",$dprefix."SCRN"], 7 => ["Inter Branch Transfer(OUT)",$dprefix."IBT-OUT"], 8 => ["Goods Received Voucher", $dprefix."GRV"], 9 => ["Inter Branch Transfer (IN)", $dprefix."IBT-IN"], 10 => ["PDF Labels Document", $dprefix."PDFL"], 11 => ["Loyalty Cards Document", $dprefix."LCL"], 12 => ["User Cards Document", $dprefix."UBC"], 13 => ["Power User Cards Document",$dprefix."UBC"], ); $tillprinter= $sys_settings{'intranet_printer'}; my %tempdb; tietempdb($database."_".$username, \%tempdb); my $bstartmonth = $sys_settings{'bstartmonth'}; my $budgetyear = $today{year}; $budgetyear-- if ($today{month}<$bstartmonth); $shop{id} = $profile->{shop_id}; $shop{name} = $profile->{profilename}; my $thisshop = $shop{id};# = $settings::shop_id; #/superuser = 0 my $abrevi = "";# = $settings::abrev; #/shop abreviature my $menu_db = $database; my $dbh = DBI->connect("DBI:mysql:$database;host=127.0.0.1",'root','fbg4ips') or die $DBI::errstr; my ($results, $subresults, $subresults1, $ref,$ref1,$ref2,$subref,$subref1); #/ database if (! exists $cacheflag{$database} ) { retreive_stocktypes($database, $profile->{shop_id}); retreive_bins($database, $profile->{shop_id}); #retreive_tax($database, $profile->{shop_id}); retreive_stockcats($database, $profile->{shop_id}); } retreive_tax($database, $profile->{shop_id}); retreive_printers();#2022-09-05 ,get rid of Storable module my $cvat = $btax{$database}{cvat}; my $nvat = $btax{$database}{nvat}; #/ calendar state from cookies if (! $R::year) { $R::year = $tempdb{current_dcyear}; $R::month = $tempdb{current_dcmonth}; } else { $tempdb{current_dcyear} = $R::year; $tempdb{current_dcmonth} = $R::month; } #~ #/---------------------------------------------------------------- #~ open DATA, "$folder/data/shopid.txt"; #~ while () {my ($shopid, $shoptitle) = split (/\:/,$_); $shopname{$shopid}=$shoptitle} #~ close DATA; #~ #/---------------------------------------------------------------- #/rollup is givving shop id if ($R::shopid) {$tempdb{current_shop_id} = $R::shopid} if ($tempdb{current_shop_id} eq "") {$tempdb{current_shop_id} = 1} { #/shop $shop{id}=$thisshop; $shop{name}=$shop{name}; #$shopname{$thisshop}; } # SERVE AJAX LOOKUPS - AJAX CODE (C) ANATOLI RADULOV # %%% %%%% %%% %% %% # %%%% %% %%%% %% %% # %% %% %% %% %% %%%% # %%%%%% % %% %%%%%% %% %% # %% %% %%% %% %% %% %% if ($R::action eq "ajax") { my $temphtml; #^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ if ($R::subaction eq "retreive_recipe_units") { #retreive recipe ingredients $results = $dbh->prepare("SELECT yield_unit FROM recipe_yields WHERE recipe_id='$R::recipe_id';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $temphtml .= ""; } elsif ($R::subaction eq "retreive_stockdates") { $temphtml .= ""; } elsif ($R::subaction eq "retreive_stockitems") { $results = $dbh->prepare(" SELECT si.id id,concat(sc.name,' - ',si.name) name, bin, units FROM stockitems si, stockcats sc WHERE sc.id = si.stockcats_id and si.f_status<100 and si.shop_id=$shop{id} and sc.f_status<100 and sc.shop_id=$shop{id} and si.type=$R::stocktype and sc.name like ? and si.name like ? order by name ;") or die $dbh->errstr(); $results->execute("%"."$R::catsfilter"."%","%"."$R::namefilter"."%") or die $results->errstr(); $temphtml .= ""; } #^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ print $temphtml; } #/ end ACTION == AJAX #--------------------------------------------------------------------------------- #--------------------------------------------------------------------------------- # END OF AJAX FUNCTIONS # %%% %%%% %%% %% %% # %%%% %% %%%% %% %% # %% %% %% %% %% %%%% # %%%%%% % %% %%%%%% %% %% # %% %% %%% %% %% %% %% #$shopname{$tempdb{current_shop_id}} if ($socketflag == 1 and $R::action eq "popup") {$template = slurpfile("emptypop.htm");} elsif ($R::action ne "ajax") {$template = slurpfile("empty.htm");} $shop{name} =~ s/\n//gsi; $shop{name} =~ s/\r//gsi; $date =~ s/\n//gsi; $date =~ s/\r//gsi; $shop{name} = $database unless $shop{name}; $template =~ s/\@shopname\@/$shop{name}/sgi; $template =~ s/\@date\@/$date/sgi; $template =~ s/\@version\@/$version/sgi; $template =~ s/\@user\@/$sessioner::thisrealname/sgi; #/***************************************************************** #/***************************************************************** #/***************************************************************** #/## START SURFING AND PROGRAMMING ALL ACTIONS!!!!!!!!!!! if ($socketflag == 1 and $R::action eq "tax") { my $temphtml; $onload="document.form1.name.focus();"; $template =~ s/\@title\@/Edit Tax Settings/sgi; $temphtml = "
";
#/## SUPPLIER EDIT COMMANDS
if (($R::command eq "Add Data" and $R::name)) {
$R::name=~ tr/a-z/A-Z/;
my $results = $dbh->prepare("INSERT INTO tax
(name,percent,date,std) values
(?,?,CURRENT_DATE,?);") or die $dbh->errstr();
$results->execute($R::name,$R::percent,$R::std ? 1 : 2) or die $results->errstr();
$mess = "DATA ADDED.";
retreive_tax($database, $shop{id});
#&extraplus::menueditor::retreive_tax;
} elsif ($R::command eq "delete") {
my $results = $dbh->prepare("delete from tax WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "ITEM DELETED.";
retreive_tax($database, $shop{id});
} elsif ($R::command eq "Modify Data" and $R::name) {
$R::name=~ tr/a-z/A-Z/;
my $results = $dbh->prepare("UPDATE tax SET
name=?,
percent=?,
std=?
WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute($R::name,$R::percent,$R::std ? 1 : 2) or die $results->errstr();
$mess = "DATA MODIFIED.";
retreive_tax($database, $shop{id});
#&extraplus::menueditor::retreive_tax;
}
#/## /COMMANDS
#/ DRAW TABLE
$temphtml .= $table{start};
$temphtml .= "";
$temphtml .= "Name Percent Date Default :COMMANDS: ";
$temphtml .= " ";
$results = $dbh->prepare("SELECT * FROM tax ORDER BY id;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$temphtml .= "";
$temphtml .= "$ref->{name} ";
$temphtml .= "$ref->{percent} ";
$temphtml .= "$ref->{date} ";
$temphtml .= "$yesno{$ref->{std}}";
$temphtml .= " ";
#/check for usage of the supplier
$subresults = $dbh->prepare("SELECT count(id) activity FROM menu_tax WHERE tax_id = $ref->{id};") or die $subresults->errstr();
$subresults->execute() or die $subresults->errstr();
$ref1 = $subresults->fetchrow_hashref();
$subresults->finish();
if ($ref1->{activity}==0) {
$temphtml .= "{id}&ptype=$R::ptype&pdate=$R::pdate&dcashid=$R::dcashid&fpurchases=$R::fpurchases\" onclick=\"return confirm('DELETE ITEM: $ref->{name} ?');\">$icons{d} ";
}
$temphtml .= " {id}&ptype=$R::ptype&pdate=$R::pdate&dcashid=$R::dcashid&fpurchases=$R::fpurchases\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
$temphtml .= "
{name}\" $calt>
{percent}\" $calt>
$ref->{date}
{std} == 1 ? "checked" : "").">
";
}
}
if ($R::command ne "modify") {
$temphtml .= "";
$temphtml .= "
";
}
$temphtml .= qq ~
Jump to PLU Items
"; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} elsif ($socketflag == 1 and $R::action eq "Closing Stock Undo") {
my $temphtml;
$template =~ s/\@title\@/UNDO STOCK TAKE/sgi;
$temphtml .= "Jump to PLU Items
"; $temphtml .= ""; $temphtml .= "
";
$onload="document.form1.name.focus();";
if ($R::command eq "Proceed") {
if (! $R::name or ! $R::reason or ! $R::take) {
$mess .= "$error PLEASE PROVIDE NAME, REASON AND SELECT STOCK TAKE DATE!";
$formvars{name}=$R::name;
$formvars{reason}=$R::reason;
$temphtml .= getform("unlock", \%formvars);
$temphtml =~ s/\@formtitle\@/UNDOING CLOSING STOCK/gsi;
$temphtml =~ s/\@action\@/$R::action/gsi;
$temphtml =~ s/\@subaction\@/$R::subaction/gsi;
$temphtml .= "";
} else {
my ($type,$stamp) = split /\|/, $R::take;
my $results = $dbh->prepare("
select * from cstock where date='$stamp' and stocktype = $type;
") or die $results->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$tempdb{"cs$type"."_$ref->{stockitems_id}"}=$ref->{qty};
}
$results = $dbh->prepare("INSERT INTO unlocked (shop_id,date,datestamp,name,reason,section)
values ('$shop{id}','$stamp',CURRENT_DATE,'$R::name',?,2);") or die $results->errstr();
$results->execute("STOCK: $stocktypes{$database}{$type} STAMP: $stamp \n REASON: \n".$R::reason) or die $results->errstr();
$results = $dbh->prepare("
UPDATE cstock SET f_status=101
where shop_id = $shop{id} and stocktype=$type
and date = '$stamp';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$R::reason =~ s/\n/
/sgi; $temphtml .= "STOCK: $stocktypes{$database}{$type}
STAMP: $stamp
UNDONE BY: $R::name
REASON: $R::reason
NOTE THAT THE DELETED STOCK ON HAND IS STORED AS NOT FINALIZED ON YOUR CLOSING STOCK EDITING SCREEN.
Please use the main menu to continue. "; } } else { $temphtml .= "Please select the stock take which you would like to undo:
"; $temphtml .= getform("unlock", undef); $temphtml =~ s/\@formtitle\@/UNDOING CLOSING STOCK/gsi; $temphtml =~ s/\@action\@/$R::action/gsi; $temphtml =~ s/\@subaction\@/$R::subaction/gsi; $temphtml .= ""; } $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} elsif ($socketflag == 1 and $R::action eq "accounts") {
my $file = "$tdir/account.html";
my $output;
my $ttemplate = Template->new(ABSOLUTE => 1,COMPILE_EXT => '.ttc');
my $vars = {
message => "", sessionusername => $username
};
my $temphtml = $ttemplate->process($file, $vars, \$output)
|| die "Template process failed: ", $ttemplate->error(), "\n";
$template =~ s/\@content\@//gsi;
$template =~ s/\@title\@/Welcome/gsi;
$template = "";
print $output;
}
elsif ($socketflag == 1 and $R::action eq "Daily Cash-up") {
my $temphtml;
my %calc;
$template =~ s/\@title\@/Daily Cash-up/sgi;
$temphtml = "/sgi; $temphtml .= "STOCK: $stocktypes{$database}{$type}
STAMP: $stamp
UNDONE BY: $R::name
REASON: $R::reason
NOTE THAT THE DELETED STOCK ON HAND IS STORED AS NOT FINALIZED ON YOUR CLOSING STOCK EDITING SCREEN.
Please use the main menu to continue. "; } } else { $temphtml .= "Please select the stock take which you would like to undo:
"; $temphtml .= getform("unlock", undef); $temphtml =~ s/\@formtitle\@/UNDOING CLOSING STOCK/gsi; $temphtml =~ s/\@action\@/$R::action/gsi; $temphtml =~ s/\@subaction\@/$R::subaction/gsi; $temphtml .= ""; } $temphtml .= "
";
if ($R::subaction eq "Create New Cash-up") {
my %sqldate = dateforsql($R::date);
$onload="document.form1.name.focus();";
if ($R::command eq "Create") {
#/ query for cashup existance
$results = $dbh->prepare("SELECT id,name FROM dcash WHERE f_status<100 and shop_id = $shop{id} and DAYOFMONTH(date)='$sqldate{day}' and MONTH(date)='$sqldate{month}' and YEAR(date)='$sqldate{year}';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
$results->finish();
if ($sqldate{day} eq "" or $sqldate{month} eq "" or $sqldate{year} eq "") {
$mess = qq|$error Date incorrect!!!|;
$formvars{date} = $R::date || $date;
$formvars{name} = $R::name;
$formhtml = getform("dcash", \%formvars);
$temphtml .= $formhtml;
} elsif (! $R::name) {
$mess = qq|$error Cash-up by field is missing!!!|;
$formvars{date} = $R::date || $date;
$formhtml = getform("dcash", \%formvars);
$temphtml .= $formhtml;
} elsif ($ref->{id}) {
$mess = qq|$error Cash-up for $R::date exists it was created by $ref->{name}!!!|;
$formvars{date} = $R::date || $date;
$formvars{name} = $R::name;
$formhtml = getform("dcash", \%formvars);
$temphtml .= $formhtml;
} else {
#/check whether all the waitrons has been cached up
#/ foreach waiter select the last till id and then check whether there is trade for the waiter after this ID
my %activity;
my %opentables;
my %waitronname;
my %tabledetails;
$results = $dbh->prepare("select id,number from tilltables where shop_id='$shop{id}' and f_status<100;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while($ref = $results->fetchrow_hashref()) {
$tabledetails{number}{$ref->{id}} = $ref->{number};
}
$results = $dbh->prepare("select id,name from waitron where f_status<101;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while($ref = $results->fetchrow_hashref()) {
$waitronname{$ref->{id}} = $ref->{name}; #/ all names
#/ retreive last cashup mark
$subresults = $dbh->prepare("select
max(last_tillid)
last_tillid
from dcashitems
where waitron_id = '$ref->{id}'
and f_status<101
and shop_id=$shop{id};") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
my $lasttillid = $subref->{last_tillid};
#/retreive sales activity
$subresults = $dbh->prepare("select count(ttod.id) activity from
tillordersdetails ttod, tillorders tto, tillinvoices inv
where
ttod.shop_id=$shop{id}
and tto.shop_id=$shop{id}
and inv.shop_id=$shop{id}
and tto.f_status < 101
and ttod.f_status < 101
and ttod.id > '$lasttillid'
and ttod.waitron_id = '$ref->{id}'
and ttod.orders_id = tto.id
and ttod.stamp > DATE_SUB(CURRENT_DATE, INTERVAL 10 day)
and tto.confirm_status = 3
and inv.id = ttod.invoice
and (inv.voided is NULL or inv.voided = 0 or inv.voided = 2)
;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
if ($subref->{activity}) {$activity{$ref->{id}} = 1}
#/retreive open tables
#/TODO - only active from tilltables
my @alltables;
$subresults = $dbh->prepare("select distinct table_id
from tilltableops where shop_id=$shop{id}
and tilltableops.stamp > DATE_SUB(CURRENT_DATE, INTERVAL 10 day)
and waitron_id='$ref->{id}' and f_status<101;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
while ($subref = $subresults->fetchrow_hashref()) {
push @alltables, $subref->{table_id};
}
foreach my $tbl (@alltables) {
$subresults = $dbh->prepare("
select action
from tilltableops
where
shop_id=$shop{id}
and f_status < 101
and waitron_id = '$ref->{id}'
and table_id='$tbl'
order by id desc limit 1;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
if ($subref->{action} != 2 and $subresults->rows > 0) {
push @{ $opentables{$ref->{id}} } , $tabledetails{number}{$tbl};
}
}
}
if ((! scalar keys %activity) and (! scalar keys %opentables)) {
$subresults = $dbh->prepare("select max(id) last_tillid from tillordersdetails where f_status<101;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
my $lasttillid_all = $subref->{last_tillid};
#/create virtual cashup
$mess = "New Cash-up created for the following date: ".$R::date." by ".$R::name;
$results = $dbh->prepare("delete from tillonholds;;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
#/insert the new cash-up here:
$R::name=~ tr/a-z/A-Z/;
$results = $dbh->prepare("INSERT INTO dcash (name,date,time,shop_id,last_tillid)
values ('$R::name','$sqldate{year}-$sqldate{month}-$sqldate{day}',CURRENT_TIME,'$shop{id}', '$lasttillid_all');") or die $dbh->errstr();
$results->execute() or die $results->errstr();
} else {
$mess = "PROBLEM CREATING CASHUP";
if (scalar keys %activity) {
$temphtml .= qq|ERROR - USERS NOT CASH-UP FROM PREVIOUS CASH-UP
|; $temphtml .= " $waitronname{$_}" foreach (keys %activity);
$temphtml .= "
"; } if (scalar keys %opentables) { $temphtml .= qq|ERROR - TABLES STILL OPEN FOR USERS
|; foreach my $waitron (keys %opentables) { $temphtml .= " $waitronname{$waitron} Table(s): " ;
foreach my $tblnum (@{$opentables{$waitron}}) {
$temphtml .= " $tblnum";
}
}
$temphtml .= "
"; } } } #/last else of creation checkup #/ command Create } else { #/ print the form for this subaction - Create New Cash-up $mess = "Please provide the information required:"; $formvars{date} = $R::date || $date; $formhtml = getform("dcash", \%formvars); $temphtml .= $formhtml; } $temphtml .= "
"; } #/ end subaction Create New Cash-up if ($R::subaction eq "editcash") { $temphtml .= qq~ ~; #/ prepare waitrons my (%waitrons, %allwaitrons, %demowaitrons); #/ note: sorting of the waitrons must be done using array not hash! my $results = $dbh->prepare("SELECT id, name,active FROM waitron WHERE f_status<100 AND shop_id=$shop{id} ORDER BY name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $allwaitrons{$ref->{id}} = $ref->{name}; $waitrons{$ref->{id}} = $ref->{name} if ($ref->{active} == 1); $demowaitrons{$ref->{id}} = 1 if ($ref->{name} =~ /!DEMO!/sgi) } $onload=""; %calc=(); #/RESET my $lockflag; my $pettycash; my $pdate; my $thisdate_lasttillid; $results = $dbh->prepare("SELECT id,pettycash,name, DAYOFMONTH(date) day, MONTH(date) month, YEAR(date) year, last_tillid, date,f_lock FROM dcash WHERE f_status<100 and shop_id = $shop{id} and id=$R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $cashupdate; $cashupdate->{day} = $ref->{day}; $cashupdate->{month} = $ref->{month}; $cashupdate->{year} = $ref->{year}; $lockflag = $ref->{f_lock}; $thisdate_lasttillid = $ref->{last_tillid}*1; $pdate = $ref->{date}; $pettycash = $ref->{pettycash}; $temphtml .="
";
$temphtml .= "You are editing Cash-up for $ref->{day} $months{$ref->{month}} $ref->{year} created by $ref->{name}
"; #next tillid $results = $dbh->prepare("select last_tillid from dcash where last_tillid > '$thisdate_lasttillid' and f_status<100 and shop_id = $shop{id} limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $nextdate_lasttillid = $ref->{last_tillid}*1; $temphtml .= qq~ ~ if ($lockflag == 1); #/ Javascript for IE #/document.all.td_avrnet.innerHTML=pr(avrnet); #/document.all.td_grossto.innerHTML=pr(grossto); #/document.all.td_nettoexc.innerHTML=pr(nettoexc); #/document.all.td_vat.innerHTML=pr(vat); #/document.all.td_cash.innerHTML=pr(cash); #/document.all.td_grosscash.innerHTML=pr(grosscash); #/## COMMANDS CASHUP #/AUTO COMMANDS if (($R::command eq "Cash Up User" and $R::autocash > 0) and $demowaitrons{$R::autocash} != 1) { my $lastcashuptillid; $subresults = $dbh->prepare("select last_tillid from dcashitems where waitron_id=$R::autocash and f_status<101 order by id desc limit 1; ") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); $subref = $subresults->fetchrow_hashref(); $lastcashuptillid = $subref->{last_tillid} / 1; #verify for open table first. $results = $dbh->prepare("SELECT waitron.name, t1.table_id, tilltables.type, tilltables.number, t1.action, t1.id, t1.waitron_id FROM tilltableops AS t1, (SELECT MAX(id) AS maxid FROM tilltableops WHERE f_status< 100 GROUP BY table_id) AS t2, tilltables, waitron WHERE waitron_id = ? AND t1.f_status< 100 AND t1.id = t2.maxid AND tilltables.id = t1.table_id AND t1.waitron_id = waitron.id AND t1.action = 1 GROUP BY t1.table_id; ;") or die $dbh->errstr(); $results->execute($R::autocash) or die $results->errstr(); ######## Cashup possible if (! $results->rows) { my @my_invoices; my @my_finaldata; $results = $dbh->prepare(" SELECT MAX(tillinvoices_id) lastinvoice FROM dcashinvoices WHERE dcashitems_id = (SELECT MAX(id) FROM dcashitems WHERE waitron_id=? AND f_status < 101); ;") or die $dbh->errstr(); $results->execute($R::autocash) or die $results->errstr(); my $lastinvoice = $ref->{lastinvoice}; $results = $dbh->prepare(" SELECT DISTINCT todds.invoice FROM tillordersdetails todds left join tillinvoices inv on inv.id = todds.invoice WHERE todds.id > ? AND todds.invoice > 0 and todds.waitron_id = ? and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) and inv.f_status < 101 ORDER BY todds.invoice; ;") or die $dbh->errstr(); $results->execute($lastcashuptillid, $R::autocash) or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { push @my_invoices, $ref->{invoice}; } #~ open(DDD , ">/dddd.txt"); foreach my $winv (@my_invoices) { $results = $dbh->prepare(" select inv.client_id, inv.account, inv.discount, inv.cash, inv.ccard, inv.ccardtype, inv.cheque, inv.id inv, inv.void_reason, inv.supervised_by, inv.voided, time(inv.stamp) invtime, date(inv.stamp) invdate from tillinvoices inv where id = ? and f_status < 101 ;") or die $dbh->errstr(); $results->execute($winv) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $ldata; $ldata->{client_id} = $ref->{client_id}; $ldata->{account} = $ref->{account}; $ldata->{discount} = $ref->{discount}; $ldata->{cash} = $ref->{cash}; $ldata->{ccard} = $ref->{ccard}; $ldata->{ccardtype} = $ref->{ccardtype}; $ldata->{cheque} = $ref->{cheque}; $ldata->{inv} = $ref->{inv}; $ldata->{void_reason} = $ref->{void_reason}; $ldata->{supervised_by} = $ref->{supervised_by}; $ldata->{voided} = $ref->{voided}; $ldata->{invtime} = $ref->{invtime}; $ldata->{invdate} = $ref->{invdate}; my @refs; $results = $dbh->prepare(" select (ttod.price*ttod.qty) price, (ttod.discount*ttod.qty) plu_discount, ttod.price uprice, ttod.qty qty, ttod.yield yield, ttod.name name, ttod.orders_id, ttod.plu from tillordersdetails ttod where invoice = ? and f_status < 101 and plu > 0 ;") or die $dbh->errstr(); $results->execute($winv) or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { push @refs, { 'price' => $ref->{price}, 'plu_discount' => $ref->{plu_discount}, 'uprice' => $ref->{uprice}, 'qty' => $ref->{qty}, 'yield' => $ref->{yield}, 'name' => $ref->{name}, 'orders_id' => $ref->{orders_id}, 'plu' => $ref->{plu} }; } foreach my $todds (@refs) { $ldata->{price} = $todds->{price}; $ldata->{plu_discount} = $todds->{plu_discount}; $ldata->{uprice} = $todds->{uprice}; $ldata->{qty} = $todds->{qty}; $ldata->{yield} = $todds->{yield}; $ldata->{name} = $todds->{name}; $ldata->{orderid} = $todds->{orders_id}; $ldata->{plu} = $todds->{plu}; $results = $dbh->prepare(" select type, id menuid from menu where id = ? ;") or die $dbh->errstr(); $results->execute($todds->{plu}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $ldata->{type} = $ref->{type}; $ldata->{menuid} = $ref->{menuid}; $results = $dbh->prepare(" select confirm_status, tableops_id from tillorders where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($todds->{orders_id}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my ($confirm_status, $tableops_id) = ($ref->{confirm_status}, $ref->{tableops_id}); if ($confirm_status == 3) { $results = $dbh->prepare(" select tabops.visitors covers, tabops.id tabopsid, tabops.table_id from tilltableops tabops where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($tableops_id) or die $results->errstr(); my $ref = $results->fetchrow_hashref(); $ldata->{covers} = $ref->{covers}; $ldata->{tabopsid} = $ref->{tabopsid}; my $table_id = $ref->{table_id}; $results = $dbh->prepare(" select tilltables.type ttype, tilltables.id tilltables_id from tilltables where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($table_id) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $ldata->{ttype} = $ref->{ttype}; $ldata->{tilltables_id} = $ref->{tilltables_id}; push @my_finaldata, { %{$ldata} }; # use anonymous hash ref to create a copy of the hash #~ foreach my $kk ( keys %{$ldata} ) { #~ print DDD "$kk\t$ldata->{$kk}\t"; #~ } #~ print DDD "\n"; } #confirm_status = 3 } # items } #invoices #~ close (DDD); #~ open (DDD, ">/dddd.dump"); #~ print DDD Dumper(\@my_finaldata); #~ close DDD; #old cashup---------- #~ $results = $dbh->prepare(" #~ select #~ inv.client_id client_id, #~ inv.account account, #~ inv.discount discount, #~ inv.cash cash, #~ inv.ccard ccard, #~ inv.ccardtype ccardtype, #~ inv.cheque cheque, #~ (ttod.price*ttod.qty) price, #~ ttod.price uprice, #~ (ttod.discount*ttod.qty) plu_discount, #~ ttod.qty qty, #~ ttod.yield yield, #~ inv.id inv, #~ inv.void_reason void_reason, #~ inv.supervised_by supervised_by, #~ menu.type type, #~ menu.id menuid, #~ inv.voided voided, #~ tto.id orderid, #~ tabops.visitors covers, #~ tilltables.type ttype, #~ tilltables.id tilltables_id, #~ ttod.name name, #~ tabops.id tabopsid, #~ IF (menu.novat, 0,ttod.price/(100 + $cvat)*$cvat*qty) vat, #~ time(inv.stamp) invtime, #~ date(inv.stamp) invdate #~ from #~ tillordersdetails ttod, #~ tillorders tto, #~ menu, #~ tillinvoices inv, #~ tilltableops tabops, #~ tilltables #~ where #~ ttod.f_status < 101 #~ and tto.f_status <101 #~ and ttod.waitron_id = $R::autocash #~ and ttod.id > '$lastcashuptillid' #~ and tabops.id = tto.tableops_id #~ and tilltables.id = tabops.table_id #~ and ttod.orders_id = tto.id #~ and tto.confirm_status=3 #~ and ttod.plutype = 1 #~ and ttod.menusource = 1 #~ and menu.id = ttod.plu #~ and inv.id = ttod.invoice #~ and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) #~ ;") or die $dbh->errstr(); #~ $results->execute() or die $results->errstr(); my $dci_turnover_total; my %dcinv_total_turnover; my %cash_control=(); my %costcenter_turnovers; my %bills; my %client_id; my %returnsfromwaitrons; my %returnsauthor; my %void_reasons; my %invoicestamp; #determine itemcount my %itemCnts; my %discounts; foreach my $ref (@my_finaldata){ $discounts{$ref->{inv}} = $ref->{discount}; if(!($ref->{plu_discount} > 0)){ $itemCnts{$ref->{inv}} += $ref->{qty}; } } my $itemDiscount=0; my %tax_cache; foreach my $ref (@my_finaldata) { #old #~ while ($ref = $results->fetchrow_hashref()) { #retreive tax for the PLU if (! exists $tax_cache{$ref->{menuid}}) { $subresults = $dbh->prepare(" select * from menu_tax where menu_id='$ref->{menuid}';") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { $tax_cache{$ref->{menuid}} += $tax{$database}{$subref->{tax_id}}{tax}; } } if($ref->{ttype} == 4){ $ref->{vat} = 0; }else{ #FORMULA dcashvat: # (price - discount)/(100 + vat))*vat # (((ttod.price - ((ttod.discount/itemCnt)*ttod.qty))/(100 + vat)*vat if($ref->{plu_discount} > 0){ $ref->{vat} = (($ref->{price} - $ref->{plu_discount})/(100 + $tax_cache{$ref->{menuid}}))*$tax_cache{$ref->{menuid}} if ($tax_cache{$ref->{menuid}} > 0); }else{ #$itemDiscount = $discounts{$ref->{inv}}/$itemCnts{$ref->{inv}}; #BY STEVEN 2019 $itemDiscount = $discounts{$ref->{inv}}/$itemCnts{$ref->{inv}} if $itemCnts{$ref->{inv}} >0; $ref->{vat} = (($ref->{price} - ($itemDiscount * $ref->{qty}))/(100 + $tax_cache{$ref->{menuid}}))*$tax_cache{$ref->{menuid}} if ($tax_cache{$ref->{menuid}} > 0); } } $client_id{$ref->{inv}} = $ref->{client_id}; #total turnovers #DCASHITEMS TURNOVER TOTAL $dci_turnover_total += $ref->{price}; #DCASHINVOICES TURNOVER TOTAL $dcinv_total_turnover{$ref->{inv}} += $ref->{price}; $cash_control{discounts}{$ref->{inv}} = 0; #CONTROLS if($ref->{voided} == 2) { $cash_control{returns}{$ref->{inv}} += $ref->{price}; $void_reasons{$ref->{inv}} = $ref->{void_reason}; } else { if ($ref->{ttype} == 4) { #4$cash_control{staffpromos}{$ref->{inv}} += $ref->{price}; #/ 4 = staff $cash_control{staffpromos}{$ref->{inv}} += $ref->{price} unless $ref->{voided} ==1; #/ 4 = staff } else { $cash_control{accounts}{$ref->{inv}} = $ref->{account} if ($ref->{account}!=0 ); $cash_control{discounts}{$ref->{inv}} = $ref->{discount} if ($ref->{discount}!=0); $cash_control{plu_discount}{$ref->{inv}} += $ref->{plu_discount}; #if ($ref->{ccardtype} eq "eft" and $ref->{ccard}!=0) { #CHANGE STEVEN 2019-06-03 if (($ref->{ccardtype} eq "eft" || $ref->{ccardtype} eq "zapper" || $ref->{ccardtype} eq "snapscan") and $ref->{ccard}!=0) { $cash_control{eft}{$ref->{inv}} = $ref->{ccard}; } elsif ($ref->{ccard}!=0) { $cash_control{creditcards}{$ref->{inv}} = $ref->{ccard}; } $cash_control{cheques}{$ref->{inv}} = $ref->{cheque} if ($ref->{cheque}!=0); } } # print "$ref->{vat} DEBUG"; $cash_control{vat}{$ref->{inv}} += $ref->{vat} if ($ref->{vat}); $cash_control{date}{$ref->{inv}} = $ref->{invdate}; $cash_control{time}{$ref->{inv}} = $ref->{invtime}; $cash_control{tilltables_id}{$ref->{inv}} = $ref->{tilltables_id}; $bills{$ref->{inv}}{covers}{$ref->{covers}} = 1 if ($ref->{ttype} != 4 and ! $ref->{voided}); $cash_control{supervised_by}{$ref->{inv}} = $ref->{supervised_by} if ($ref->{supervised_by}>0); #DCASHTURNOVERS TURNOVER TOTAL $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_turnover} += ($ref->{price}-$ref->{vat}); $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_staffpromo} += ($ref->{price} - $ref->{vat}) if ($ref->{ttype} == 4); $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_returns} += ( $ref->{price} - $ref->{vat}) if ($ref->{voided} == 2); if ($ref->{plu_discount} != 0 and $ref->{price} != 0) { $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{plu_discounts} += ( $ref->{plu_discount} - $ref->{vat}*($ref->{plu_discount}/$ref->{price}) ); } $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{vat} += $ref->{vat}; } #distribute discount not from PLUS by Stock Types and calculate total discount per cost center foreach my $invoice (keys %{$cash_control{discounts}}) { my $discounts_from_supervisor_net = $cash_control{discounts}{$invoice} - $cash_control{plu_discount}{$invoice}*1; #debug print "$invoice discounts_from_supervisor_net = $discounts_from_supervisor_net
"; $discounts_from_supervisor_net = $discounts_from_supervisor_net - $discounts_from_supervisor_net/$dcinv_total_turnover{$invoice}* $cash_control{vat}{$invoice} if ($dcinv_total_turnover{$invoice}>0); foreach my $costcenter ( keys %{ $costcenter_turnovers{$invoice} } ) { my $factor; $factor = ($costcenter_turnovers{$invoice}{$costcenter}{total_turnover}+ $costcenter_turnovers{$invoice}{$costcenter}{vat} ) / $dcinv_total_turnover{$invoice} if ($dcinv_total_turnover{$invoice}>0); #% of total invoice $costcenter_turnovers{$invoice}{$costcenter}{total_discounts} = $costcenter_turnovers{$invoice}{$costcenter}{plu_discounts} + #discounts from PLUs $discounts_from_supervisor_net * $factor; } } #covers foreach my $invoice (keys %dcinv_total_turnover) { my $ccovers; $ccovers += $_ foreach (keys %{$bills{$invoice}{covers}} ); $cash_control{covers}{$invoice} = $ccovers; } $subresults = $dbh->prepare("select max(id) last_tillid from tillordersdetails where f_status<101;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); $subref = $subresults->fetchrow_hashref(); my $lasttillid_all = $subref->{last_tillid}; #recordtype = 1 - system entry | NULL manual entry my $results = $dbh->prepare("INSERT INTO dcashitems ( dcash_id, shop_id, waitron_id, last_tillid, recordtype, dci_turnover_total, date, time, theshift ) values ( '$R::id', '$shop{id}', '$R::autocash', '$lasttillid_all', 1, '$dci_turnover_total', CURRENT_DATE, CURRENT_TIME, '$R::shift' ) ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results->finish(); $results = $dbh->prepare("SELECT LAST_INSERT_ID() lastid FROM dcashitems limit 1;"); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $lastid = $ref->{lastid}; #loop and insert all invoices foreach my $inv (sort {$a <=> $b} keys %dcinv_total_turnover) { $results = $dbh->prepare(" INSERT INTO dcashinvoices ( dcashitems_id, shop_id, tillinvoices_id, dcinv_turnover_total, covers, tilltables_id, tillclients_id, date, time, eft_total, creditcards_total, cheque_total, accounts_total, returns_total, discount_total, staffpromo_total, vat_total ) values ( '$lastid', '$shop{id}', '$inv', '$dcinv_total_turnover{$inv}', '$cash_control{covers}{$inv}', '$cash_control{tilltables_id}{$inv}', '$client_id{$inv}', '$cash_control{date}{$inv}', '$cash_control{time}{$inv}', '$cash_control{eft}{$inv}', '$cash_control{creditcards}{$inv}', '$cash_control{cheques}{$inv}', '$cash_control{accounts}{$inv}', '$cash_control{returns}{$inv}', ?, '$cash_control{staffpromos}{$inv}', '$cash_control{vat}{$inv}' ) ;") or die $dbh->errstr(); $results->execute( #$cash_control{discounts}{$inv} < 0 ? 0 : $cash_control{discounts}{$inv} $cash_control{discounts}{$inv} ) or die $results->errstr(); $results->finish(); $results = $dbh->prepare("SELECT LAST_INSERT_ID() lastid FROM dcashinvoices limit 1;"); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $lastinvid = $ref->{lastid}; foreach my $costcenter (sort {$a <=> $b} keys %{ $costcenter_turnovers{$inv} } ) { $results = $dbh->prepare("insert into dcashturnover ( shop_id, dcashinvoices_id, costcenter_id, turnover_total, returns_total, staffpromo_total, discounts_total, vat_total) values ( $shop{id}, $lastinvid, '$costcenter', '$costcenter_turnovers{$inv}{$costcenter}{total_turnover}', '$costcenter_turnovers{$inv}{$costcenter}{total_returns}', '$costcenter_turnovers{$inv}{$costcenter}{total_staffpromo}', '$costcenter_turnovers{$inv}{$costcenter}{total_discounts}', '$costcenter_turnovers{$inv}{$costcenter}{vat}' );"); $results->execute() or die $results->errstr(); $results->finish(); } } #/update returns foreach (sort {$a <=> $b} keys %{ $cash_control{returns} }) { #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,retrns,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{returns}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "REASON: $void_reasons{$_}", "SUPERVISOR: $authorizedby", $_) or die $results->errstr(); } #update accounts foreach (sort {$a <=> $b} keys %{ $cash_control{accounts} }) { $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,debtors,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{accounts}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "NOTE:", "[AUTHORIZED BY]", $_) or die $results->errstr(); } #update promos foreach (sort {$a <=> $b} keys %{ $cash_control{discounts} }) { if ( $cash_control{plu_discount}{$_} / 1 == $cash_control{discounts}{$_} / 1 && $cash_control{plu_discount}{$_} / 1 != 0) { #promotion from PLUs only $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{plu_discount}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PLU PROMOTIONS", "SPECIAL", "$_") or die $results->errstr(); } elsif ( $cash_control{plu_discount}{$_} / 1 == 0) { #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; #print "SUPER: DISCOUNT: $authorizedby"; $results = $dbh->prepare(" select * from supervisorlog where tillinvoices_id = ? ;") or die $dbh->errstr(); $results->execute($_) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $reason = $ref->{reason}; if (! $authorizedby) { $results = $dbh->prepare("select name from supervisor where id = ? and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute($ref->{supervisor}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $authorizedby = $ref->{name}; } if ($cash_control{discounts}{$_} != 0) { $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,?,?,?,1) ;") or die $dbh->errstr(); $results->execute( $reason || "PROVIDE REASON", $cash_control{discounts}{$_},# > 0 ? $cash_control{discounts}{$_} : 0 , "SUPERVISOR: $authorizedby", "$_") or die $results->errstr(); } } elsif ( $cash_control{plu_discount}{$_} / 1 != 0 and $cash_control{discounts}{$_} / 1 != $cash_control{plu_discount}{$_} / 1) { #PLU DISC PORTION $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{plu_discount}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PLU PROMOTION(S):", "SPECIAL", "$_") or die $results->errstr(); #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; #print "SUPER: DISCOUNT: $authorizedby"; $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'".($cash_control{discounts}{$_} - $cash_control{plu_discount}{$_})."',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PROVIDE REASON", "SUPERVISOR: $authorizedby", "$_") or die $results->errstr(); } } #foreach cheq with discount $mess = "DATA ADDED.
"; } else {# cashup not possible $mess = "
|; $temphtml .= "
"; } if (scalar keys %opentables) { $temphtml .= qq|ERROR - TABLES STILL OPEN FOR USERS
|; foreach my $waitron (keys %opentables) { $temphtml .= "
"; } } } #/last else of creation checkup #/ command Create } else { #/ print the form for this subaction - Create New Cash-up $mess = "Please provide the information required:"; $formvars{date} = $R::date || $date; $formhtml = getform("dcash", \%formvars); $temphtml .= $formhtml; } $temphtml .= "
"; } #/ end subaction Create New Cash-up if ($R::subaction eq "editcash") { $temphtml .= qq~ ~; #/ prepare waitrons my (%waitrons, %allwaitrons, %demowaitrons); #/ note: sorting of the waitrons must be done using array not hash! my $results = $dbh->prepare("SELECT id, name,active FROM waitron WHERE f_status<100 AND shop_id=$shop{id} ORDER BY name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $allwaitrons{$ref->{id}} = $ref->{name}; $waitrons{$ref->{id}} = $ref->{name} if ($ref->{active} == 1); $demowaitrons{$ref->{id}} = 1 if ($ref->{name} =~ /!DEMO!/sgi) } $onload=""; %calc=(); #/RESET my $lockflag; my $pettycash; my $pdate; my $thisdate_lasttillid; $results = $dbh->prepare("SELECT id,pettycash,name, DAYOFMONTH(date) day, MONTH(date) month, YEAR(date) year, last_tillid, date,f_lock FROM dcash WHERE f_status<100 and shop_id = $shop{id} and id=$R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $cashupdate; $cashupdate->{day} = $ref->{day}; $cashupdate->{month} = $ref->{month}; $cashupdate->{year} = $ref->{year}; $lockflag = $ref->{f_lock}; $thisdate_lasttillid = $ref->{last_tillid}*1; $pdate = $ref->{date}; $pettycash = $ref->{pettycash}; $temphtml .="
"; #next tillid $results = $dbh->prepare("select last_tillid from dcash where last_tillid > '$thisdate_lasttillid' and f_status<100 and shop_id = $shop{id} limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $nextdate_lasttillid = $ref->{last_tillid}*1; $temphtml .= qq~ ~ if ($lockflag == 1); #/ Javascript for IE #/document.all.td_avrnet.innerHTML=pr(avrnet); #/document.all.td_grossto.innerHTML=pr(grossto); #/document.all.td_nettoexc.innerHTML=pr(nettoexc); #/document.all.td_vat.innerHTML=pr(vat); #/document.all.td_cash.innerHTML=pr(cash); #/document.all.td_grosscash.innerHTML=pr(grosscash); #/## COMMANDS CASHUP #/AUTO COMMANDS if (($R::command eq "Cash Up User" and $R::autocash > 0) and $demowaitrons{$R::autocash} != 1) { my $lastcashuptillid; $subresults = $dbh->prepare("select last_tillid from dcashitems where waitron_id=$R::autocash and f_status<101 order by id desc limit 1; ") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); $subref = $subresults->fetchrow_hashref(); $lastcashuptillid = $subref->{last_tillid} / 1; #verify for open table first. $results = $dbh->prepare("SELECT waitron.name, t1.table_id, tilltables.type, tilltables.number, t1.action, t1.id, t1.waitron_id FROM tilltableops AS t1, (SELECT MAX(id) AS maxid FROM tilltableops WHERE f_status< 100 GROUP BY table_id) AS t2, tilltables, waitron WHERE waitron_id = ? AND t1.f_status< 100 AND t1.id = t2.maxid AND tilltables.id = t1.table_id AND t1.waitron_id = waitron.id AND t1.action = 1 GROUP BY t1.table_id; ;") or die $dbh->errstr(); $results->execute($R::autocash) or die $results->errstr(); ######## Cashup possible if (! $results->rows) { my @my_invoices; my @my_finaldata; $results = $dbh->prepare(" SELECT MAX(tillinvoices_id) lastinvoice FROM dcashinvoices WHERE dcashitems_id = (SELECT MAX(id) FROM dcashitems WHERE waitron_id=? AND f_status < 101); ;") or die $dbh->errstr(); $results->execute($R::autocash) or die $results->errstr(); my $lastinvoice = $ref->{lastinvoice}; $results = $dbh->prepare(" SELECT DISTINCT todds.invoice FROM tillordersdetails todds left join tillinvoices inv on inv.id = todds.invoice WHERE todds.id > ? AND todds.invoice > 0 and todds.waitron_id = ? and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) and inv.f_status < 101 ORDER BY todds.invoice; ;") or die $dbh->errstr(); $results->execute($lastcashuptillid, $R::autocash) or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { push @my_invoices, $ref->{invoice}; } #~ open(DDD , ">/dddd.txt"); foreach my $winv (@my_invoices) { $results = $dbh->prepare(" select inv.client_id, inv.account, inv.discount, inv.cash, inv.ccard, inv.ccardtype, inv.cheque, inv.id inv, inv.void_reason, inv.supervised_by, inv.voided, time(inv.stamp) invtime, date(inv.stamp) invdate from tillinvoices inv where id = ? and f_status < 101 ;") or die $dbh->errstr(); $results->execute($winv) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $ldata; $ldata->{client_id} = $ref->{client_id}; $ldata->{account} = $ref->{account}; $ldata->{discount} = $ref->{discount}; $ldata->{cash} = $ref->{cash}; $ldata->{ccard} = $ref->{ccard}; $ldata->{ccardtype} = $ref->{ccardtype}; $ldata->{cheque} = $ref->{cheque}; $ldata->{inv} = $ref->{inv}; $ldata->{void_reason} = $ref->{void_reason}; $ldata->{supervised_by} = $ref->{supervised_by}; $ldata->{voided} = $ref->{voided}; $ldata->{invtime} = $ref->{invtime}; $ldata->{invdate} = $ref->{invdate}; my @refs; $results = $dbh->prepare(" select (ttod.price*ttod.qty) price, (ttod.discount*ttod.qty) plu_discount, ttod.price uprice, ttod.qty qty, ttod.yield yield, ttod.name name, ttod.orders_id, ttod.plu from tillordersdetails ttod where invoice = ? and f_status < 101 and plu > 0 ;") or die $dbh->errstr(); $results->execute($winv) or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { push @refs, { 'price' => $ref->{price}, 'plu_discount' => $ref->{plu_discount}, 'uprice' => $ref->{uprice}, 'qty' => $ref->{qty}, 'yield' => $ref->{yield}, 'name' => $ref->{name}, 'orders_id' => $ref->{orders_id}, 'plu' => $ref->{plu} }; } foreach my $todds (@refs) { $ldata->{price} = $todds->{price}; $ldata->{plu_discount} = $todds->{plu_discount}; $ldata->{uprice} = $todds->{uprice}; $ldata->{qty} = $todds->{qty}; $ldata->{yield} = $todds->{yield}; $ldata->{name} = $todds->{name}; $ldata->{orderid} = $todds->{orders_id}; $ldata->{plu} = $todds->{plu}; $results = $dbh->prepare(" select type, id menuid from menu where id = ? ;") or die $dbh->errstr(); $results->execute($todds->{plu}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $ldata->{type} = $ref->{type}; $ldata->{menuid} = $ref->{menuid}; $results = $dbh->prepare(" select confirm_status, tableops_id from tillorders where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($todds->{orders_id}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my ($confirm_status, $tableops_id) = ($ref->{confirm_status}, $ref->{tableops_id}); if ($confirm_status == 3) { $results = $dbh->prepare(" select tabops.visitors covers, tabops.id tabopsid, tabops.table_id from tilltableops tabops where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($tableops_id) or die $results->errstr(); my $ref = $results->fetchrow_hashref(); $ldata->{covers} = $ref->{covers}; $ldata->{tabopsid} = $ref->{tabopsid}; my $table_id = $ref->{table_id}; $results = $dbh->prepare(" select tilltables.type ttype, tilltables.id tilltables_id from tilltables where f_status < 101 and id = ? ;") or die $dbh->errstr(); $results->execute($table_id) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $ldata->{ttype} = $ref->{ttype}; $ldata->{tilltables_id} = $ref->{tilltables_id}; push @my_finaldata, { %{$ldata} }; # use anonymous hash ref to create a copy of the hash #~ foreach my $kk ( keys %{$ldata} ) { #~ print DDD "$kk\t$ldata->{$kk}\t"; #~ } #~ print DDD "\n"; } #confirm_status = 3 } # items } #invoices #~ close (DDD); #~ open (DDD, ">/dddd.dump"); #~ print DDD Dumper(\@my_finaldata); #~ close DDD; #old cashup---------- #~ $results = $dbh->prepare(" #~ select #~ inv.client_id client_id, #~ inv.account account, #~ inv.discount discount, #~ inv.cash cash, #~ inv.ccard ccard, #~ inv.ccardtype ccardtype, #~ inv.cheque cheque, #~ (ttod.price*ttod.qty) price, #~ ttod.price uprice, #~ (ttod.discount*ttod.qty) plu_discount, #~ ttod.qty qty, #~ ttod.yield yield, #~ inv.id inv, #~ inv.void_reason void_reason, #~ inv.supervised_by supervised_by, #~ menu.type type, #~ menu.id menuid, #~ inv.voided voided, #~ tto.id orderid, #~ tabops.visitors covers, #~ tilltables.type ttype, #~ tilltables.id tilltables_id, #~ ttod.name name, #~ tabops.id tabopsid, #~ IF (menu.novat, 0,ttod.price/(100 + $cvat)*$cvat*qty) vat, #~ time(inv.stamp) invtime, #~ date(inv.stamp) invdate #~ from #~ tillordersdetails ttod, #~ tillorders tto, #~ menu, #~ tillinvoices inv, #~ tilltableops tabops, #~ tilltables #~ where #~ ttod.f_status < 101 #~ and tto.f_status <101 #~ and ttod.waitron_id = $R::autocash #~ and ttod.id > '$lastcashuptillid' #~ and tabops.id = tto.tableops_id #~ and tilltables.id = tabops.table_id #~ and ttod.orders_id = tto.id #~ and tto.confirm_status=3 #~ and ttod.plutype = 1 #~ and ttod.menusource = 1 #~ and menu.id = ttod.plu #~ and inv.id = ttod.invoice #~ and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) #~ ;") or die $dbh->errstr(); #~ $results->execute() or die $results->errstr(); my $dci_turnover_total; my %dcinv_total_turnover; my %cash_control=(); my %costcenter_turnovers; my %bills; my %client_id; my %returnsfromwaitrons; my %returnsauthor; my %void_reasons; my %invoicestamp; #determine itemcount my %itemCnts; my %discounts; foreach my $ref (@my_finaldata){ $discounts{$ref->{inv}} = $ref->{discount}; if(!($ref->{plu_discount} > 0)){ $itemCnts{$ref->{inv}} += $ref->{qty}; } } my $itemDiscount=0; my %tax_cache; foreach my $ref (@my_finaldata) { #old #~ while ($ref = $results->fetchrow_hashref()) { #retreive tax for the PLU if (! exists $tax_cache{$ref->{menuid}}) { $subresults = $dbh->prepare(" select * from menu_tax where menu_id='$ref->{menuid}';") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { $tax_cache{$ref->{menuid}} += $tax{$database}{$subref->{tax_id}}{tax}; } } if($ref->{ttype} == 4){ $ref->{vat} = 0; }else{ #FORMULA dcashvat: # (price - discount)/(100 + vat))*vat # (((ttod.price - ((ttod.discount/itemCnt)*ttod.qty))/(100 + vat)*vat if($ref->{plu_discount} > 0){ $ref->{vat} = (($ref->{price} - $ref->{plu_discount})/(100 + $tax_cache{$ref->{menuid}}))*$tax_cache{$ref->{menuid}} if ($tax_cache{$ref->{menuid}} > 0); }else{ #$itemDiscount = $discounts{$ref->{inv}}/$itemCnts{$ref->{inv}}; #BY STEVEN 2019 $itemDiscount = $discounts{$ref->{inv}}/$itemCnts{$ref->{inv}} if $itemCnts{$ref->{inv}} >0; $ref->{vat} = (($ref->{price} - ($itemDiscount * $ref->{qty}))/(100 + $tax_cache{$ref->{menuid}}))*$tax_cache{$ref->{menuid}} if ($tax_cache{$ref->{menuid}} > 0); } } $client_id{$ref->{inv}} = $ref->{client_id}; #total turnovers #DCASHITEMS TURNOVER TOTAL $dci_turnover_total += $ref->{price}; #DCASHINVOICES TURNOVER TOTAL $dcinv_total_turnover{$ref->{inv}} += $ref->{price}; $cash_control{discounts}{$ref->{inv}} = 0; #CONTROLS if($ref->{voided} == 2) { $cash_control{returns}{$ref->{inv}} += $ref->{price}; $void_reasons{$ref->{inv}} = $ref->{void_reason}; } else { if ($ref->{ttype} == 4) { #4$cash_control{staffpromos}{$ref->{inv}} += $ref->{price}; #/ 4 = staff $cash_control{staffpromos}{$ref->{inv}} += $ref->{price} unless $ref->{voided} ==1; #/ 4 = staff } else { $cash_control{accounts}{$ref->{inv}} = $ref->{account} if ($ref->{account}!=0 ); $cash_control{discounts}{$ref->{inv}} = $ref->{discount} if ($ref->{discount}!=0); $cash_control{plu_discount}{$ref->{inv}} += $ref->{plu_discount}; #if ($ref->{ccardtype} eq "eft" and $ref->{ccard}!=0) { #CHANGE STEVEN 2019-06-03 if (($ref->{ccardtype} eq "eft" || $ref->{ccardtype} eq "zapper" || $ref->{ccardtype} eq "snapscan") and $ref->{ccard}!=0) { $cash_control{eft}{$ref->{inv}} = $ref->{ccard}; } elsif ($ref->{ccard}!=0) { $cash_control{creditcards}{$ref->{inv}} = $ref->{ccard}; } $cash_control{cheques}{$ref->{inv}} = $ref->{cheque} if ($ref->{cheque}!=0); } } # print "$ref->{vat} DEBUG"; $cash_control{vat}{$ref->{inv}} += $ref->{vat} if ($ref->{vat}); $cash_control{date}{$ref->{inv}} = $ref->{invdate}; $cash_control{time}{$ref->{inv}} = $ref->{invtime}; $cash_control{tilltables_id}{$ref->{inv}} = $ref->{tilltables_id}; $bills{$ref->{inv}}{covers}{$ref->{covers}} = 1 if ($ref->{ttype} != 4 and ! $ref->{voided}); $cash_control{supervised_by}{$ref->{inv}} = $ref->{supervised_by} if ($ref->{supervised_by}>0); #DCASHTURNOVERS TURNOVER TOTAL $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_turnover} += ($ref->{price}-$ref->{vat}); $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_staffpromo} += ($ref->{price} - $ref->{vat}) if ($ref->{ttype} == 4); $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{total_returns} += ( $ref->{price} - $ref->{vat}) if ($ref->{voided} == 2); if ($ref->{plu_discount} != 0 and $ref->{price} != 0) { $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{plu_discounts} += ( $ref->{plu_discount} - $ref->{vat}*($ref->{plu_discount}/$ref->{price}) ); } $costcenter_turnovers{$ref->{inv}}{$ref->{type}}{vat} += $ref->{vat}; } #distribute discount not from PLUS by Stock Types and calculate total discount per cost center foreach my $invoice (keys %{$cash_control{discounts}}) { my $discounts_from_supervisor_net = $cash_control{discounts}{$invoice} - $cash_control{plu_discount}{$invoice}*1; #debug print "$invoice discounts_from_supervisor_net = $discounts_from_supervisor_net
"; $discounts_from_supervisor_net = $discounts_from_supervisor_net - $discounts_from_supervisor_net/$dcinv_total_turnover{$invoice}* $cash_control{vat}{$invoice} if ($dcinv_total_turnover{$invoice}>0); foreach my $costcenter ( keys %{ $costcenter_turnovers{$invoice} } ) { my $factor; $factor = ($costcenter_turnovers{$invoice}{$costcenter}{total_turnover}+ $costcenter_turnovers{$invoice}{$costcenter}{vat} ) / $dcinv_total_turnover{$invoice} if ($dcinv_total_turnover{$invoice}>0); #% of total invoice $costcenter_turnovers{$invoice}{$costcenter}{total_discounts} = $costcenter_turnovers{$invoice}{$costcenter}{plu_discounts} + #discounts from PLUs $discounts_from_supervisor_net * $factor; } } #covers foreach my $invoice (keys %dcinv_total_turnover) { my $ccovers; $ccovers += $_ foreach (keys %{$bills{$invoice}{covers}} ); $cash_control{covers}{$invoice} = $ccovers; } $subresults = $dbh->prepare("select max(id) last_tillid from tillordersdetails where f_status<101;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); $subref = $subresults->fetchrow_hashref(); my $lasttillid_all = $subref->{last_tillid}; #recordtype = 1 - system entry | NULL manual entry my $results = $dbh->prepare("INSERT INTO dcashitems ( dcash_id, shop_id, waitron_id, last_tillid, recordtype, dci_turnover_total, date, time, theshift ) values ( '$R::id', '$shop{id}', '$R::autocash', '$lasttillid_all', 1, '$dci_turnover_total', CURRENT_DATE, CURRENT_TIME, '$R::shift' ) ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results->finish(); $results = $dbh->prepare("SELECT LAST_INSERT_ID() lastid FROM dcashitems limit 1;"); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $lastid = $ref->{lastid}; #loop and insert all invoices foreach my $inv (sort {$a <=> $b} keys %dcinv_total_turnover) { $results = $dbh->prepare(" INSERT INTO dcashinvoices ( dcashitems_id, shop_id, tillinvoices_id, dcinv_turnover_total, covers, tilltables_id, tillclients_id, date, time, eft_total, creditcards_total, cheque_total, accounts_total, returns_total, discount_total, staffpromo_total, vat_total ) values ( '$lastid', '$shop{id}', '$inv', '$dcinv_total_turnover{$inv}', '$cash_control{covers}{$inv}', '$cash_control{tilltables_id}{$inv}', '$client_id{$inv}', '$cash_control{date}{$inv}', '$cash_control{time}{$inv}', '$cash_control{eft}{$inv}', '$cash_control{creditcards}{$inv}', '$cash_control{cheques}{$inv}', '$cash_control{accounts}{$inv}', '$cash_control{returns}{$inv}', ?, '$cash_control{staffpromos}{$inv}', '$cash_control{vat}{$inv}' ) ;") or die $dbh->errstr(); $results->execute( #$cash_control{discounts}{$inv} < 0 ? 0 : $cash_control{discounts}{$inv} $cash_control{discounts}{$inv} ) or die $results->errstr(); $results->finish(); $results = $dbh->prepare("SELECT LAST_INSERT_ID() lastid FROM dcashinvoices limit 1;"); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $lastinvid = $ref->{lastid}; foreach my $costcenter (sort {$a <=> $b} keys %{ $costcenter_turnovers{$inv} } ) { $results = $dbh->prepare("insert into dcashturnover ( shop_id, dcashinvoices_id, costcenter_id, turnover_total, returns_total, staffpromo_total, discounts_total, vat_total) values ( $shop{id}, $lastinvid, '$costcenter', '$costcenter_turnovers{$inv}{$costcenter}{total_turnover}', '$costcenter_turnovers{$inv}{$costcenter}{total_returns}', '$costcenter_turnovers{$inv}{$costcenter}{total_staffpromo}', '$costcenter_turnovers{$inv}{$costcenter}{total_discounts}', '$costcenter_turnovers{$inv}{$costcenter}{vat}' );"); $results->execute() or die $results->errstr(); $results->finish(); } } #/update returns foreach (sort {$a <=> $b} keys %{ $cash_control{returns} }) { #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,retrns,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{returns}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "REASON: $void_reasons{$_}", "SUPERVISOR: $authorizedby", $_) or die $results->errstr(); } #update accounts foreach (sort {$a <=> $b} keys %{ $cash_control{accounts} }) { $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,debtors,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{accounts}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "NOTE:", "[AUTHORIZED BY]", $_) or die $results->errstr(); } #update promos foreach (sort {$a <=> $b} keys %{ $cash_control{discounts} }) { if ( $cash_control{plu_discount}{$_} / 1 == $cash_control{discounts}{$_} / 1 && $cash_control{plu_discount}{$_} / 1 != 0) { #promotion from PLUs only $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{plu_discount}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PLU PROMOTIONS", "SPECIAL", "$_") or die $results->errstr(); } elsif ( $cash_control{plu_discount}{$_} / 1 == 0) { #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; #print "SUPER: DISCOUNT: $authorizedby"; $results = $dbh->prepare(" select * from supervisorlog where tillinvoices_id = ? ;") or die $dbh->errstr(); $results->execute($_) or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $reason = $ref->{reason}; if (! $authorizedby) { $results = $dbh->prepare("select name from supervisor where id = ? and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute($ref->{supervisor}) or die $results->errstr(); $ref = $results->fetchrow_hashref(); $authorizedby = $ref->{name}; } if ($cash_control{discounts}{$_} != 0) { $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,?,?,?,1) ;") or die $dbh->errstr(); $results->execute( $reason || "PROVIDE REASON", $cash_control{discounts}{$_},# > 0 ? $cash_control{discounts}{$_} : 0 , "SUPERVISOR: $authorizedby", "$_") or die $results->errstr(); } } elsif ( $cash_control{plu_discount}{$_} / 1 != 0 and $cash_control{discounts}{$_} / 1 != $cash_control{plu_discount}{$_} / 1) { #PLU DISC PORTION $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'$cash_control{plu_discount}{$_}',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PLU PROMOTION(S):", "SPECIAL", "$_") or die $results->errstr(); #/fetch the authorized by (supervisor) $results = $dbh->prepare("select name from supervisor where id = '$cash_control{supervised_by}{$_}' and shop_id=$shop{id};") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $authorizedby = $ref->{name}; #print "SUPER: DISCOUNT: $authorizedby"; $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id,doc,promo,authorized,invoice,recordtype) values ('$R::id', '$shop{id}',?,'".($cash_control{discounts}{$_} - $cash_control{plu_discount}{$_})."',?,?,1) ;") or die $dbh->errstr(); $results->execute( "PROVIDE REASON", "SUPERVISOR: $authorizedby", "$_") or die $results->errstr(); } } #foreach cheq with discount $mess = "DATA ADDED.
"; } else {# cashup not possible $mess = "
(R: = REASON; CHK: = CHEQUE #)
"; #/## COMMANDS PDR if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { my $results = $dbh->prepare("INSERT INTO pdrcash (dcash_id, shop_id, doc, promo, debtors, retrns, authorized, invoice, recordtype) values ('$R::id','$shop{id}','$R::doc', '$R::promo', '$R::debtors','$R::retrns', '$R::authorized',$R::tillinvoices_id,0) ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare(" update dcashinvoices set f_status=1, tillclients_id=$R::clients_id where tillinvoices_id=$R::tillinvoices_id and f_status<100 and shop_id=$shop{id}; ") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "DATA ADDED."; } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE pdrcash SET f_status=101 WHERE dcash_id=$R::id and id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "LINE DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my $results = $dbh->prepare("UPDATE pdrcash SET doc='$R::doc', promo='$R::promo', debtors='$R::debtors', retrns='$R::retrns', authorized='$R::authorized', invoice='$R::tillinvoices_id', f_status=1 WHERE dcash_id=$R::id and id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); if ($R::clients_id) { $results = $dbh->prepare(" update dcashinvoices set f_status=1, tillclients_id=$R::clients_id where tillinvoices_id=$R::tillinvoices_id and f_status<100 and shop_id=$shop{id}; ") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } $mess = "DATA MODIFIED."; } #/## /COMMANDS #/## DRAW TABLE PDR my %client_names; $results = $dbh->prepare("SELECT concat(clientgroup,' -> ',name) name,id from $sharedclients.tillclients where f_status < 100 and shop_id=$sharedclients_shopid; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $client_names{$ref->{id}} = $ref->{name}; } $temphtml .= $table{start}; $temphtml .= qq ~
(DESCRIPTION/NAME, ID of document)
Difference for account of Management ".pr($calc{diff})."
Jump to Cash-up
"; $temphtml .= "
"; $temphtml .= ""; $temphtml .= ""; } elsif ($R::subaction eq "unlock") { my $results = $dbh->prepare("SELECT id,name,DAYOFMONTH(date) day,MONTH(date) month,YEAR(date) year,f_lock FROM dcash WHERE f_status<100 and shop_id=$shop{id} and id=$R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); $temphtml .= "You are unlocking CASH-UP for $ref->{day} $months{$ref->{month}} $ref->{year} created by $ref->{name}
"; $onload="document.form1.name.focus();"; if ($R::command eq "Proceed") { if (! $R::name or ! $R::reason) { $mess .= "$error PLEASE PROVIDE NAME AND REASON!"; $formvars{name}=$R::name; $formvars{reason}=$R::reason; $temphtml .= getform("unlock", \%formvars); $temphtml =~ s/\@formtitle\@/UNLOCKING DAILY CASHUP/gsi; $temphtml =~ s/\@action\@/Daily Cash-up/gsi; $temphtml =~ s/\@subaction\@/unlock/gsi; $temphtml .= ""; } else { my $results = $dbh->prepare("select date from dcash where shop_id = $shop{id} and id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); $results = $dbh->prepare("INSERT INTO unlocked (shop_id,date,datestamp,name,reason,section) values ('$shop{id}','$ref->{date}',CURRENT_DATE,'$R::name',?,1);") or die $results->errstr(); $results->execute($R::reason) or die $results->errstr(); $results = $dbh->prepare("UPDATE dcash SET f_lock=1,f_status=1 where shop_id = $shop{id} and id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $R::reason =~ s/\n/
/sgi; $temphtml .= "Succeded by $R::name
REASON: $R::reason
"; $temphtml .= ""; } } else { $temphtml .= getform("unlock", undef); $temphtml =~ s/\@formtitle\@/UNLOCKING DAILY CASHUP/gsi; $temphtml =~ s/\@action\@/Daily Cash-up/gsi; $temphtml =~ s/\@subaction\@/unlock/gsi; $temphtml .= ""; } } else { #/ no subaction - just view the calendar #/ Main Cashup lists & delete action. #/## delete dcash if ($R::subaction eq "delete") { my $results = $dbh->prepare("SELECT id,name,DAYOFMONTH(date) day,MONTH(date) month,YEAR(date) year,date,f_lock FROM dcash WHERE f_status<100 and shop_id = $shop{id} and id=$R::id and f_lock =1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { my $pdate = $ref->{date}; #/TODO - delete everything from retail that is subclass to this day?? There musnt be anything my $results = $dbh->prepare("UPDATE dcash SET f_status=101 where shop_id = $shop{id} and id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare("UPDATE dcashitems SET f_status=101 where dcash_id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare("UPDATE purchases SET f_status=101 where date = '$pdate' and type = 3;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare("UPDATE loans SET f_status=101 where date = '$pdate';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare("UPDATE pdrcash SET f_status=101 where dcash_id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess="
Cash-up for $ref->{day} $months{$ref->{month}} $ref->{year} created by $ref->{name} was deleted."; } else {$mess="
$error nonexisting record"} } #/## //delete dcash #/## lock dcash if ($R::subaction eq "lock") { my $results = $dbh->prepare("SELECT id,name,DAYOFMONTH(date) day,MONTH(date) month,YEAR(date) year,f_lock FROM dcash WHERE f_status<100 and shop_id = $shop{id} and id=$R::id and f_lock =1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { my $results = $dbh->prepare("UPDATE dcash SET f_lock=2,f_status=1 where shop_id = $shop{id} and id = $R::id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess="
Cash-up for $ref->{day} $months{$ref->{month}} $ref->{year} created by $ref->{name} was locked."; } else {$mess="
$error nonexisting record"} } #/## //lock dcash my $weekbg; $cellcolor = ''; my $yearresults = $dbh->prepare("SELECT DISTINCT YEAR(date) year FROM dcash WHERE f_status<100 and shop_id = $shop{id} order by date;") or die $dbh->errstr(); $yearresults->execute() or die $yearresults->errstr(); $temphtml .= "
"; $temphtml .= "
| |||||||||||||||||||||||
$ref->{name} user has the same pin."; } $results = $dbh->prepare(" select * from supervisor where pin = ? and active=1 and f_status < 101;") or die $dbh->errstr(); $results->execute($R::pin) or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} super user has the same pin."; } if ($pinflag eq '') { if ( $R::name ){ $R::name=~ tr/a-z/A-Z/; #~ my $results = $dbh->prepare(" #~ update waitron set table_id = null where table_id = '$R::table_id' #~ ;") or die $dbh->errstr(); #~ $results->execute() or die $results->errstr(); my $results = $dbh->prepare("INSERT INTO waitron (top_category,table_id, shop_id,name, gender, passid, address, phone, active, pin,commission) values ('$R::top_category','$R::table_id', $shop{id},'$R::name','$R::gender','$R::passid','$R::address','$R::phone','1',?,'$R::commission');") or die $dbh->errstr(); $results->execute($R::pin) or die $results->errstr(); $mess = "DATA ADDED."; } } else { $mess = "ERROR PIN CODE PROBLEM $pinflag"; } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE waitron SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "USER DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my $pinflag = ''; $results = $dbh->prepare(" select * from waitron where pin = ? and active=1 and id != '$R::itemid' and f_status < 100;") or die $dbh->errstr(); $results->execute($R::pin) or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} user has the same pin."; } $results = $dbh->prepare(" select * from supervisor where pin = ? and active=1 and f_status < 101;") or die $dbh->errstr(); $results->execute($R::pin) or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} super user has the same pin."; } if ($pinflag eq '') { if ($R::name and $R::name !~ /!DEMO!/sgi){ $R::name=~ tr/a-z/A-Z/; my $results = $dbh->prepare(" update waitron set table_id = null where table_id = '$R::table_id' ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $results = $dbh->prepare("UPDATE waitron SET top_category='$R::top_category', table_id='$R::table_id', name='$R::name', gender='$R::gender', passid='$R::passid', address='$R::address', phone='$R::phone', active='$R::active', pin=?, commission='$R::commission', f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::pin) or die $results->errstr(); $mess = "DATA MODIFIED."; } } else { $mess = "ERROR PIN CODE PROBLEM $pinflag"; } } #/## /COMMANDS $template =~ s/\@title\@/Edit Users/sgi; $temphtml = "
";
$temphtml .= $table{start};
$temphtml .= "";
$temphtml .= "
Name
Gender
ID/PASSPORT NR
Address
Phone
Status
Commission %
Pin
QM Table
Top Category
:COMMANDS: ";
$temphtml .= " ";
my $tabledetails;
$results = $dbh->prepare("SELECT *
FROM tilltables
WHERE shop_id = $shop{id} and f_status<100;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$tabledetails->{number}->{$ref->{id}} = $ref->{number};
$tabledetails->{type}->{ $ref->{id} } = $tilltabletype{ $ref->{type} };
$tabledetails->{description}->{ $ref->{id} } = $ref->{description} ;
}
$results = $dbh->prepare("
SELECT
waitron.*,
substring_index(group_concat(dcashitems.date order by dcashitems.date desc), ',', 1) last_active,
substring_index(group_concat(TIMESTAMPDIFF(DAY,dcashitems.date,current_date) order by dcashitems.date desc), ',', 1) last_active_days,
menu_categories.name top_name
FROM waitron
left join dcashitems on waitron.id = dcashitems.waitron_id and dcashitems.f_status < 100
left join menu_categories on waitron.top_category = menu_categories.id
WHERE
waitron.f_status<100
group by waitron.id
ORDER BY active, name DESC;
") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
my $warning;
if ($ref->{active} == 1 and $ref->{last_active_days} > 30 ) {
$warning = " ";
}
$ref->{pin} = '*hidden';
$temphtml .= "";
$temphtml .= "{last_active_days} days.\" NOWRAP ".( $ref->{name} =~ /!DEMO!/ ? "style=\"background-color: red\"" : "").">
".($ref->{active} == 2 ? "not ACTIVE: " : "")."{id}&dcash_id=$R::dcash_id\">$ref->{name}
$warning $gender{$ref->{gender}}
$ref->{passid}
$ref->{address}
$ref->{phone}
$wstatus{$ref->{active}}
$ref->{commission}
$ref->{pin}
";
if ($ref->{table_id}) {
$temphtml .= "$tabledetails->{number}->{$ref->{table_id}} ( $tabledetails->{type}->{$ref->{table_id}} ) $tabledetails->{description}->{$ref->{table_id}}";
} else {
$temphtml .= "N/a";
}
$temphtml .= " ";
if ($ref->{top_category}) {
$temphtml .= "$ref->{top_name}";
} else {
$temphtml .= "MAIN";
}
$temphtml .= " ";
$temphtml .= "";
$subresults = $dbh->prepare("SELECT count(id) activity FROM dcashitems WHERE shop_id=$shop{id} and f_status<100 and waitron_id = $ref->{id};") or die $subresults->errstr();
$subresults->execute() or die $subresults->errstr();
$ref1 = $subresults->fetchrow_hashref();
$subresults->finish();
my $activity;
$activity = $ref1->{activity};
$subresults = $dbh->prepare("SELECT count(id) activity FROM tillordersdetails WHERE shop_id=$shop{id} and f_status<100 and waitron_id = $ref->{id};") or die $subresults->errstr();
$subresults->execute() or die $subresults->errstr();
$ref1 = $subresults->fetchrow_hashref();
$subresults->finish();
$activity += $ref1->{activity};
if (! $activity) {
$temphtml .= "{id}&dcash_id=$R::dcash_id\" onclick=\"return confirm('DELETE USER: $ref->{name} ?');\">$icons{d}";
}
$temphtml .= " {id}&dcash_id=$R::dcash_id\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
my (%gsel,%asel);
$gsel{$ref->{gender}}="selected";
$asel{$ref->{active}}="selected";
$temphtml .= "
{name}\" $calt>
{passid}\" $calt>
{address}\" $calt>
{phone}\" $calt>
{commission}\" $calt>%
{pin}\" $calt> ";
}
}
if ($R::command ne "modify") {
$temphtml .= "";
$temphtml .= "
*
%
";
}
$temphtml .= qq ~
Return to CASH-UP
" if ($R::dcash_id ne ""); $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end Waitron editor
elsif ($socketflag == 1 and $R::action eq "Edit Employees") {
my $temphtml;
$onload="document.form1.name.focus();";
#/## /Employees EDIT COMMANDS
if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) {
if ($R::name ){
$R::name=~ tr/a-z/A-Z/;
my $results = $dbh->prepare("INSERT INTO employees
(shop_id,name, gender, passid, address, phone, active, pin) values
($shop{id},'$R::name','$R::gender','$R::passid','$R::address','$R::phone','1','$R::pin');") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "DATA ADDED.";
}
} elsif ($R::command eq "delete") {
my $results = $dbh->prepare("UPDATE employees SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "employees DELETED.";
} elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) {
if ($R::name ){
$R::name=~ tr/a-z/A-Z/;
my $results = $dbh->prepare("UPDATE employees SET
name='$R::name',
gender='$R::gender',
passid='$R::passid',
address='$R::address',
phone='$R::phone',
active='$R::active',
pin='$R::pin',
f_status=1
WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "DATA MODIFIED.";
}
}
#/## /COMMANDS
$template =~ s/\@title\@/Edit employeess/sgi;
$temphtml = "Return to CASH-UP
" if ($R::dcash_id ne ""); $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$temphtml .= $table{start};
$temphtml .= "";
$temphtml .= "Name Gender ID # Address Phone Status Pin :COMMANDS: ";
$temphtml .= " ";
$results = $dbh->prepare("SELECT * FROM employees WHERE shop_id = $shop{id} and f_status<100 ORDER BY active, id DESC;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$ref->{pin} =~ tr/0-9/\*/s;
$temphtml .= "";
$temphtml .= "$ref->{name} $gender{$ref->{gender}} $ref->{passid} $ref->{address} $ref->{phone} $wstatus{$ref->{active}} $ref->{pin} ";
$temphtml .= "";
#/$subresults = $dbh->prepare("SELECT count(id) activity FROM dcashitems WHERE shop_id=$shop{id} and f_status<100 and waitron_id = $ref->{id};") or die $subresults->errstr();
#/$subresults->execute() or die $subresults->errstr();
#/$ref1 = $subresults->fetchrow_hashref();
#/$subresults->finish();
#/
#/activity on till side check
if (1) {
$temphtml .= "{id}&dcash_id=$R::dcash_id\" onclick=\"return confirm('DELETE USER: $ref->{name} ?');\">$icons{d}";
}
$temphtml .= " {id}&dcash_id=$R::dcash_id\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
my (%gsel,%asel);
$gsel{$ref->{gender}}="selected";
$asel{$ref->{active}}="selected";
$temphtml .= "
{name}\" $calt>
{passid}\" $calt>
{address}\" $calt>
{phone}\" $calt>
{pin}\" $calt>
";
}
}
if ($R::command ne "modify") {
$temphtml .= "";
$temphtml .= "
*
";
}
$temphtml .= qq ~
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end Employee editor
elsif ($socketflag == 1 and $R::action eq "Repair") {
my $dir = opendir(DDD,"/wwwroot/");
my @stfiles = grep {/\.STATION/igs} readdir(DDD);
foreach my $file (@stfiles) {
unlink "/wwwroot/$file";
}
close DDD;
my $temphtml;
$template =~ s/\@title\@/Repair/sgi;
$temphtml = "";
$temphtml .= "
";
$temphtml .= "
PLEASE READ THE FOLLOWING:
Please note that if your Msg_text column doesn't say OK you have lost data records in one of your intranet tables (eg. purchases, POS orders...) in case you get similar message (eg. number of rows changed) you should print this screen and then fax it to the head office. If you have more questions about the repair procedure and backup restoring please contact Anatoli or Bibi. Thank you for your time. Now you can continue working with the intranet and the POS. Everything should be back to normal."; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
}
elsif ($socketflag == 1 and $R::action eq "Edit Supervisors") {
my $temphtml;
$onload="document.form1.name.focus();";
#/## /SUPERVISOR EDIT COMMANDS
if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) {
my $pinflag = '';
$results = $dbh->prepare("
select * from waitron where pin = '$R::pin' and active=1 and f_status < 101;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
if ($ref->{id}) {
$pinflag .= "| ";
$temphtml .= " REPAIR/OPTIMIZE FINISHED "; my @repair_tables; $results = $dbh->prepare("show tables;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while (my @tarr = $results->fetchrow_array()) { push @repair_tables, $tarr[0]; } $results = $dbh->prepare("show tables from spaceusers;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while (my @tarr = $results->fetchrow_array()) { push @repair_tables, 'spaceusers.'.$tarr[0]; } $temphtml .= " REPAIR RESULTS "; $temphtml .= "
OPTIMIZE RESULTS "; $temphtml .= "
|
PLEASE READ THE FOLLOWING:
Please note that if your Msg_text column doesn't say OK you have lost data records in one of your intranet tables (eg. purchases, POS orders...) in case you get similar message (eg. number of rows changed) you should print this screen and then fax it to the head office. If you have more questions about the repair procedure and backup restoring please contact Anatoli or Bibi. Thank you for your time. Now you can continue working with the intranet and the POS. Everything should be back to normal."; $temphtml .= "
$ref->{name} user has the same pin."; } $results = $dbh->prepare(" select * from supervisor where pin = '$R::pin' and active=1 and f_status<100;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} super user has the same pin."; } if ($pinflag eq '') { if ($R::name) { $R::name=~ tr/a-z/A-Z/; my $results = $dbh->prepare("INSERT INTO supervisor (shop_id,name, gender, passid, address, phone, active, pin) values ($shop{id},'$R::name','$R::gender','$R::passid','$R::address','$R::phone','1','$R::pin');") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "DATA ADDED."; } } else { $mess = "ERROR PIN CODE PROBLEM $pinflag"; } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE supervisor SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "SUPERVISOR DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my $pinflag = ''; $results = $dbh->prepare(" select * from waitron where pin = '$R::pin' and active=1 and f_status<100;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} user has the same pin."; } $results = $dbh->prepare(" select * from supervisor where pin = '$R::pin' and active=1 and id != '$R::itemid' and f_status < 101;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{id}) { $pinflag .= "
$ref->{name} super user has the same pin."; } if ($pinflag eq '') { if ($R::name) { $R::name=~ tr/a-z/A-Z/; my $results = $dbh->prepare("UPDATE supervisor SET name='$R::name', gender='$R::gender', passid='$R::passid', address='$R::address', phone='$R::phone', active='$R::active', pin='$R::pin', f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "DATA MODIFIED."; } } else { $mess = "ERROR PIN CODE PROBLEM $pinflag"; } } #/## /COMMANDS $template =~ s/\@title\@/Edit Supervisors/sgi; $temphtml = "
";
$temphtml .= $table{start};
$temphtml .= "";
$temphtml .= "Name Gender ID # Address Phone Status Pin :COMMANDS: ";
$temphtml .= " ";
$results = $dbh->prepare("SELECT * FROM supervisor WHERE shop_id = $shop{id} and f_status<100 ORDER BY active, id DESC;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$ref->{pin} =~ tr/0-9/\*/s;
$temphtml .= "";
$temphtml .= "$ref->{name} $gender{$ref->{gender}} $ref->{passid} $ref->{address} $ref->{phone} $wstatus{$ref->{active}} $ref->{pin} ";
$temphtml .= "";
$subresults = $dbh->prepare("SELECT count(id) activity FROM tillinvoices WHERE shop_id=$shop{id} and f_status<100 and supervised_by = $ref->{id};") or die $subresults->errstr();
$subresults->execute() or die $subresults->errstr();
$ref1 = $subresults->fetchrow_hashref();
$subresults->finish();
#/activity on till side check
if (! $ref1->{activity}) {
$temphtml .= "{id}&dcash_id=$R::dcash_id\" onclick=\"return confirm('DELETE USER: $ref->{name} ?');\">$icons{d}";
}
$temphtml .= " {id}&dcash_id=$R::dcash_id\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
my (%gsel,%asel);
$gsel{$ref->{gender}}="selected";
$asel{$ref->{active}}="selected";
$temphtml .= "
{name}\" $calt>
{passid}\" $calt>
{address}\" $calt>
{phone}\" $calt>
{pin}\" $calt>
";
}
}
if ($R::command ne "modify") {
$temphtml .= "";
$temphtml .= "
*
";
}
$temphtml .= qq ~
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end Supervisor editor
elsif ($socketflag == 1 and $R::action eq "Edit Tables") {
my $temphtml;
$onload="document.form1.number.focus();";
#/$onload="document.form1.name.focus();";
#/## /TABLES EDIT COMMANDS
if (($R::command eq "Add Data" and $R::number) or ($R::command eq "" and $R::hcommand eq "Add Data")) {
$R::name=~ tr/a-z/A-Z/;
my $results = $dbh->prepare("delete from tilltables where f_status = 101;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results = $dbh->prepare("INSERT INTO tilltables
(shop_id,number, description, type, seats, location, ordering) values
($shop{id},'$R::number',?,'$R::type','$R::seats','$R::location', '$R::ordering');") or die $dbh->errstr();
$results->execute($R::description) or die $results->errstr();
$mess = "DATA ADDED.";
} elsif ($R::command eq "delete" and '$R::itemid') {
my $results = $dbh->prepare("select number from tilltables WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $number = $ref->{number};
my $data = eval(tvis_readtmp($database));
foreach my $stage (keys %{ $data->{stages} }) {
delete $data->{stages}->{$stage}->{tables}->{$number};
}
tvis_writetmp(Dumper($data), $database);
$results = $dbh->prepare("delete from tilltables WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "TABLE DELETED.";
} elsif (($R::command eq "Modify Data" and $R::number) or ($R::command eq "" and $R::hcommand eq "Modify Data")) {
my $results = $dbh->prepare("UPDATE tilltables SET
number='$R::number',
description=?,
type='$R::type',
seats='$R::seats',
location='$R::location',
active='$R::active',
f_status=1,
ordering='$R::ordering'
WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute($R::description) or die $results->errstr();
$mess = "DATA MODIFIED.";
if ($R::active == 2) {
my $results = $dbh->prepare("select number from tilltables WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $number = $ref->{number};
my $data = eval(tvis_readtmp($database));
foreach my $stage (keys %{ $data->{stages} }) {
delete $data->{stages}->{$stage}->{tables}->{$number};
}
tvis_writetmp(Dumper($data), $database);
}
}
#/## /COMMANDS
$template =~ s/\@title\@/Edit Tables/sgi;
$temphtml = "";
$temphtml .= " Edit Floor Plans
"; $temphtml .= $table{start}; $temphtml .= "";
if ($tillmode != 2) {
$temphtml .= "Table Number Description Type Nr. of Seats Location Status Position :COMMANDS: ";
} else {
$temphtml .= "Order Placeholder Description Type Location Status Position :COMMANDS: ";
}
$temphtml .= " ";
$results = $dbh->prepare("SELECT * FROM
tilltables WHERE shop_id = $shop{id} and f_status<100
ORDER BY active DESC, ordering ASC, number ASC;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$temphtml .= "";
if ($tillmode != 2) {
$temphtml .= "$ref->{number} $ref->{description} $tilltabletype{$ref->{type}} $ref->{seats} $tilltablelocation{$ref->{location}} $wstatus{$ref->{active}} $ref->{ordering} ";
} else {
$temphtml .= "$ref->{number} $ref->{description} $tilltabletype{$ref->{type}} $tilltablelocation{$ref->{location}} $wstatus{$ref->{active}} $ref->{ordering} ";
}
$temphtml .= "";
$subresults = $dbh->prepare("SELECT count(id) activity FROM tilltableops WHERE shop_id=$shop{id} and f_status<100 and table_id = $ref->{id};") or die $subresults->errstr();
$subresults->execute() or die $subresults->errstr();
$ref1 = $subresults->fetchrow_hashref();
$subresults->finish();
#/activity on till side check
if (! $ref1->{activity}) {
$temphtml .= "{id}\" onclick=\"return confirm('DELETE TABLE: $ref->{number} ?');\">$icons{d}";
}
$temphtml .= " {id}\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
my (%tsel,%lsel,%asel);
$tsel{$ref->{type}}="selected";
$lsel{$ref->{location}}="selected";
$asel{$ref->{active}}="selected";
$temphtml .= "
{number}\" $calt>
{description}\" $calt>
{seats}\" $calt> ";
} else {
#$temphtml .= "{seats}\" $calt> ";
}
$temphtml .= "{ordering}\" name=ordering> ";
}
}
if ($R::command ne "modify") {
$temphtml .= "";
$temphtml .= "
*
";
}
$temphtml .= qq ~
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end Tables editor
#/#
#/# STOCK
#/#
elsif ($socketflag == 1 and $R::action eq "Stock Acceptance") {
my $temphtml;
my $finalizeflag;
#/SAVE CSTOCK QTY -> TIED HASH
my @pars = $q->param; #/save if unsaved
foreach (@pars) {
if ($_ =~ /^(cs$tempdb{stocktype}_)(\d+)/) {
$tempdb{"cs$tempdb{stocktype}_$2"}=$q->param("cs$tempdb{stocktype}_$2");
}
}
#/### FIFO's and things
my $stocktype=$tempdb{stocktype};
#/get all stock in memory: name, bin, units
my %stockdetails;
$results = $dbh->prepare("select id,name,bin,units from stockitems where f_status<100 and type=$stocktype and shop_id=$shop{id} and stockitem <> 2;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$stockdetails{name}{$ref->{id}} = $ref->{name};
$stockdetails{bin}{$ref->{id}} = $ref->{bin};
$stockdetails{units}{$ref->{id}} = $ref->{units};
}
#/get the last id from purchases at the moment;
$results = $dbh->prepare("select max(id) id from purchases where f_status < 100 and shop_id=$shop{id}") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $max_pid = $ref->{id};
#/determine last pid for the stocktype from PREVIOUS STOCK
$results = $dbh->prepare("select last_pid,date from cstock where f_status<100 and stocktype=$stocktype and shop_id=$shop{id} order by id desc limit 1;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $last_pid = $ref->{last_pid}*1;
my $last_date = $ref->{date};
$last_date = "1900-01-01" if (! $last_date);
#/purchases sinse last pid excluding
my %last_purchases;
$results = $dbh->prepare("select si.id id, si.name name, si.units units, si.bin bin, sum(pu.qty) qty, sum(pu.price*pu.qty) totalprice, pu.price uprice
from purchases pu,stockitems si
where
si.f_status<100 and pu.f_status<100
and si.shop_id=$shop{id}
and pu.shop_id=si.shop_id
and si.id=pu.stockitems_id
and si.type=$stocktype
and pu.id>$last_pid #/cs:last_pid
and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0)
group by si.id
order by name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_purchases{$ref->{id}}{qty} = $ref->{qty};
$last_purchases{$ref->{id}}{value} = $ref->{totalprice};
}
#/last closing stock qty and value
my %last_closingstock;
$results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty, sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice
from cstock cs, stockitems si
where
si.f_status<100 and cs.f_status<100
and si.shop_id=$shop{id}
and cs.shop_id=si.shop_id
and si.id=cs.stockitems_id
and si.type=$stocktype
and cs.last_pid=$last_pid #/cs:last_pid
and cs.date='$last_date'
group by si.id
order by si.name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_closingstock{$ref->{id}}{qty} = $ref->{qty};
$last_closingstock{$ref->{id}}{value} = $ref->{stockvalue};
}
#/last credit notes - since last_pid
my %last_creditnotes;
$results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.price*pu.qty) totalprice, pu.price uprice
from purchases pu,stockitems si
where
si.f_status<100 and pu.f_status<100
and si.shop_id=$shop{id}
and pu.shop_id=si.shop_id
and si.id=pu.stockitems_id
and si.type=$stocktype
and pu.id>$last_pid #/cs:last_pid
and pu.cnote = 1
group by si.id
order by name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_creditnotes{$ref->{id}}{qty} = $ref->{qty};
$last_creditnotes{$ref->{id}}{value} = $ref->{totalprice};
}
#/calculate the availables;
my %availables;
foreach my $id (keys %{$stockdetails{name}}) {
my $available_qty;
$available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty});
#/$last_creditnotes{$ref->{id}}{qty} used because of need to store pvalue in this table
next if ($available_qty <= 0 and ! $last_creditnotes{$id}{qty} and $show_noqty_stock == -1);
$availables{$id} = $available_qty;
}
#/by bin
my $sortage = 'name';
#/retreive all stockIDs with their FIFO last pids
my %fifodetails;
$results = $dbh->prepare("select stockitems_id id,debited_pid,credit_left from cstock
where f_status<100 and cstock.shop_id=$shop{id} and date='$last_date'
and stocktype=$stocktype;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$fifodetails{$ref->{id}}{debited_pid} = $ref->{debited_pid};
$fifodetails{$ref->{id}}{credit_left} = $ref->{credit_left};
}
#/retreive debited_pid, credit_left from older stock, and not previous, because of missing record in previous. It is missing because the item had no availability
foreach my $id ( keys %last_purchases ) {
if (! exists $fifodetails{$id}) {
$results = $dbh->prepare("
select debited_pid,credit_left from cstock where f_status<100 and shop_id=$shop{id}
and stockitems_id=$id order by date desc limit 1;
") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
$fifodetails{$id}{debited_pid} = $ref->{debited_pid};
$fifodetails{$id}{credit_left} = $ref->{credit_left};
}
}
my %purchases_for_use;
foreach my $id (keys %availables) {
$results = $dbh->prepare("
SELECT pu.id id,
CASE
WHEN pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0 then 0
WHEN pu.cnote = 1 then 1
END cnote,
pu.qty qty,
pu.price price
from purchases pu
where
pu.f_status<100
and pu.shop_id=$shop{id}
and pu.stockitems_id='$id'
and pu.id>='$fifodetails{$id}{debited_pid}'
and pu.qty > 0
;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$purchases_for_use{$id}{$ref->{id}}{qty} = $ref->{qty};
$purchases_for_use{$id}{$ref->{id}}{price} = $ref->{price};
$purchases_for_use{$id}{$ref->{id}}{cnote} = $ref->{cnote};
}
}
my %fiforesultz;
foreach my $id (keys %availables) {
( $fiforesultz{$id}{available_value},
$fiforesultz{$id}{creditnotes_value},
$fiforesultz{$id}{credits_left},
$fiforesultz{$id}{debited_pid},
$fiforesultz{$id}{usage_qty},
$fiforesultz{$id}{usage_value},
$fiforesultz{$id}{cs_qty},
$fiforesultz{$id}{cs_value}
) = fifo_hash_for_use($id,
$last_closingstock{$id}{qty},
$last_closingstock{$id}{value},
$fifodetails{$id}{debited_pid},
$fifodetails{$id}{credit_left},
\%last_creditnotes,
\%last_purchases,
$last_pid,
\%purchases_for_use,
\%tempdb
);
}
$results = $dbh->prepare("
select current_time stamp;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $Current_Timestamp = $ref->{stamp};
#/remove this in one month time.
#$results = $dbh->prepare("alter table cstock change credit_left credit_left decimal(7,3);") or die $dbh->errstr();
#$results->execute() or die $results->errstr();
my %sqldate = dateforsql($R::date);
$results = $dbh->prepare("
insert into cstock (shop_id,
stockitems_id,
stocktype,
value,
pvalue,
last_pid,
qty,
debited_pid,
credit_left,
date)
values (
'$shop{id}',
?,
'$stocktype',
?,
?,
'$max_pid',
?,
?,
?,
'$sqldate{year}-$sqldate{month}-$sqldate{day} $Current_Timestamp');
") or die $dbh->errstr();
foreach my $id (keys %availables) {
#/pvalue is cnotes value
$results->execute($id,
$fiforesultz{$id}{cs_value},
$fiforesultz{$id}{creditnotes_value},
$fiforesultz{$id}{cs_qty},
$fiforesultz{$id}{debited_pid},
$fiforesultz{$id}{credits_left})
or die $results->errstr();
}
#/## Done.
#/DISPLAY SOME INFO ON THE SCREEN
$template =~ s/\@title\@/FINISHED EDITING CLOSING STOCK/sgi;
$temphtml = ""; $temphtml .= $table{start}; $temphtml .= "
";
$temphtml .= "Your Closing Stock is Finished Successfuly";
$temphtml .= "
";
$tempdb{stockflag} = 2; #/todo (enable when both is finalized?)
#/delete the saved stemps
foreach (@pars) {
if ($_ =~ /^(cs$tempdb{stocktype}_)(\d+)/) {
delete $tempdb{"cs$tempdb{stocktype}_$2"};
}
}
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end Finalize Closing Stock
elsif ($socketflag == 1 and $R::action eq "Cancel Closing Stock Edit") {
my $temphtml;
$template =~ s/\@title\@/EDITING CLOSING STOCK CANCELED/sgi;
$temphtml = "";
$temphtml .= "Your Closing Stock Editing is Canceled";
$temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
$tempdb{stockflag} = 2;
$results = $dbh->prepare("
SELECT DISTINCT pu.stockitems_id sid,si.name name, si.units units from purchases pu, stockitems si
where pu.stockitems_id = si.id
and si.type <=2
and pu.shop_id=$shop{id} and pu.f_status<100
and si.shop_id=$shop{id} and si.f_status<100
ORDER BY units,name;");
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
delete $tempdb{"cs$tempdb{stocktype}_$ref->{sid}"};
}
} #/ Cancel Closing Stock Edit
elsif ($socketflag == 1 and $R::action eq "Finalize Opening Stock") {
my $temphtml;
$template =~ s/\@title\@/FINISHED EDITING OPENING STOCK/sgi;
$temphtml = "";
$temphtml .= "Your Opening Stock editor is locked successfuly";
$temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
$tempdb{stockflag} = 2;
} #/ end Finalize Opening Stock
elsif ($socketflag == 1 and $R::action eq "Edit Closing Stock") {
$tempdb{stockflag} = 3; #/used in main menu to disable purchases && cashup
my $finalizeflag = 1;
my $sqlorder;
#/type 1 = by name; type 2=by bin
#/retreive perm stage
#/$tempdb{cstockviewtype} = $R::viewtype if ($R::viewtype);
#/permanent stage - viewtype
#/$tempdb{cstockviewtype} = 1 if (! $tempdb{cstockviewtype});
#/retreive permanent stages
#/$tempdb{stocktype} = $R::stocktype if ($R::stocktype);
#/no permanent stages yet - init
#/if (! $tempdb{stocktype}) {$tempdb{stocktype}=1};
my %ordertype = (1 => 'name', 2 => 'bin');
my $orderby = $ordertype{$tempdb{cstockviewtype}};
my $stocktype=$tempdb{stocktype};
fix_fifo( {dbh => $dbh, shop => \%shop }, $stocktype ) if $thisshop != 0; # fix fifo if not
my $temphtml;
my %calc;
$template =~ s/\@title\@/Edit Closing Stock $cstockviewtypes{$tempdb{cstockviewtype}}/sgi;
$temphtml = "";
$temphtml .= " $stocktypes{$database}{$tempdb{stocktype}} Stock Sheet
"; #/COMMANDS my $copy_save_msg = ""; if ($R::command eq "Save Closing Stock") { my @pars = $q->param; foreach (@pars) { if ($_ =~ /^(cs$tempdb{stocktype}_)(\d+)/) { $tempdb{"cs$tempdb{stocktype}_$2"}=$q->param("cs$tempdb{stocktype}_$2"); } } #let's try copy here #insert into stockissue (date,direction,stocktype,stockitems_id,qty)values (?,?,?,?,?) #direction is 2 if($q->param('copypastesave_flag') ==1){ my @val = $q->param('stockitems_id'); my $f->{stockitems_id} = @val>1 ? \@val : $val[0]; my $ids = join(',',grep{/^\d+$/}@{&ARef($f->{stockitems_id})}); if($ids){ my %sqldate = dateforsql($R::date); $results = $dbh->prepare("SELECT id, bin, units,sku ,qty FROM stockitems WHERE id in($ids)"); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { my $id = $ref->{id}; my $cs1_count = $q->param("cs$tempdb{stocktype}_$id"); $dbh->do("insert into stockissue (date,direction,stocktype,stockitems_id,qty) values (?,?,?,?,?)",undef, "$sqldate{year}-$sqldate{month}-$sqldate{day}", 2, $stocktype, $ref->{id}, $cs1_count||0 ); } $copy_save_msg = "Selected stock items have been copied."; } } } sub ARef{ my $data=shift; $data=[] unless $data; $data=[$data] unless ref($data) eq 'ARRAY'; return $data; } #/All queries for the screen #/get all stock in memory: name, bin, units my %stockdetails; $results = $dbh->prepare(" SELECT si.id,concat(sc.name,' - ',si.name) name, bin, units,sku FROM stockitems si LEFT JOIN stockcats sc ON sc.id = si.stockcats_id WHERE si.f_status<100 and si.shop_id=$shop{id} and sc.f_status<100 and sc.shop_id=$shop{id} and si.type=$stocktype; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $stockdetails{name}{$ref->{id}} = $ref->{name}; $stockdetails{bin}{$ref->{id}} = $ref->{bin}; $stockdetails{units}{$ref->{id}} = $ref->{units}; $stockdetails{sku}{$ref->{id}} = $ref->{sku}; $stockdetails{stitem_id}{$ref->{id}} = $ref->{id}; } #/determine last pid for the stocktype $results = $dbh->prepare("select last_pid,date from cstock where stocktype=$stocktype and shop_id=$shop{id} and f_status<100 order by id desc limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $last_pid = $ref->{last_pid}*1; my $last_date = $ref->{date}; $last_date = "1900-01-01" if (! $last_date); #/purchases sinse last pid excluding my %last_purchases; $results = $dbh->prepare("select si.id id, si.name name, si.units units, si.bin bin, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where si.f_status<100 and pu.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0) group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_purchases{$ref->{id}}{qty} = $ref->{qty}; $last_purchases{$ref->{id}}{value} = $ref->{totalprice}; } #/last closing stock qty and value my %last_closingstock; $results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty, sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice from cstock cs, stockitems si where cs.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and cs.shop_id=si.shop_id and si.id=cs.stockitems_id and si.type=$stocktype and cs.last_pid=$last_pid #/cs:last_pid and cs.date='$last_date' group by si.id order by si.name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_closingstock{$ref->{id}}{qty} = $ref->{qty}; $last_closingstock{$ref->{id}}{value} = $ref->{stockvalue}; } #/last credit notes - since last_pid my %last_creditnotes; $results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where pu.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and pu.cnote = 1 group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_creditnotes{$ref->{id}}{qty} = $ref->{qty}; $last_creditnotes{$ref->{id}}{value} = $ref->{totalprice}; } #/calculate the availables; my %availables; foreach my $id (keys %{$stockdetails{name}}) { my $available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty}); next if ($available_qty <= 0 and $show_noqty_stock == -1); $availables{$id} = $available_qty; } #/by bin / by name my $sortage = $orderby; #///All queries #/ #/ PRINT CURRENT CLOSING STOCK EDITOR #/ $temphtml .= $table{start}; if($copy_save_msg){ $temphtml .= qq|$copy_save_msg ";
$temphtml .= qq|
SKU
STOCK ITEM
BIN
PREVIOUS STOCK
PURCHASES
CREDIT NOTES
STOCK ON HAND
BEFORE SAVE
NEW STOCK
USAGE
|;
foreach my $id (
sort {$stockdetails{$sortage}{$a} cmp $stockdetails{$sortage}{$b}}
sort {$stockdetails{name}{$a} cmp $stockdetails{name}{$b}}
keys %availables)
{
next if ($availables{$id} <= 0 and $show_noqty_stock == -1);
if (
($availables{$id} < 0) or (rn3($availables{$id} - $tempdb{"cs$tempdb{stocktype}_$id"}) < 0)
) {
$finalizeflag = 0;
}
if ($tempdb{"cs$tempdb{stocktype}_$id"} ne "") {$linecolor=""} else {$linecolor="style=\"background-color: $errorcolor\""};
$temphtml .= "";
if($tempdb{"cs$tempdb{stocktype}_$id"} ne ""){
$temphtml .=qq|$stockdetails{sku}{$id}
$stockdetails{name}{$id}
$bins{$database}{$stockdetails{bin}{$id}}
".($last_closingstock{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_purchases{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_creditnotes{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($availables{$id} < 0 ? "
" : "")."".rn3($availables{$id})." $units{$stockdetails{units}{$id}}
".$tempdb{"cs$tempdb{stocktype}_$id"}."
".(rn3($availables{$id} - $tempdb{"cs$tempdb{stocktype}_$id"}) < 0 ? " " : "").(rn3($availables{$id} - $tempdb{"cs$tempdb{stocktype}_$id"}) )." $units{$stockdetails{units}{$id}} ";
$temphtml .= " ";
}
$temphtml .= $table{end};
$temphtml .= qq~
~;
$temphtml .= qq|
|; $temphtml .= " "; $temphtml .= qq~
SELECT STOCK TAKE DATE:
~;
$temphtml .= "
"; $temphtml .= "
"; $temphtml .= "
"; $temphtml .= qq|
|; $temphtml .= ""; $temphtml .= "
";#variance_center
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end CLOSING STOCK EDITOR
elsif ($socketflag == 1 and $R::action eq "Stock Sheet") {
my $temphtml;
my %calc;
my $sqlorder;
#/type 1 = by name; type 2=by bin
#/retreive perm stage
$tempdb{cstockviewtype} = $R::viewtype if ($R::viewtype);
#/permanent stage - viewtype
$tempdb{cstockviewtype} = 1 if (! $tempdb{cstockviewtype});
#/retreive permanent stages
$tempdb{stocktype} = $R::stocktype if ($R::stocktype);
#/no permanent stages yet - init
$tempdb{stocktype} = (sort {$a <=> $b} keys %{ $stocktypes{$database} } )[0] if (! $tempdb{stocktype});
my %ordertype = (1 => 'name', 2 => 'bin');
my $orderby = $ordertype{$tempdb{cstockviewtype}};
$template =~ s/\@title\@/View Stock $cstockviewtypes{$tempdb{cstockviewtype}}/sgi;
$temphtml = ""; #/COMMANDS my $copy_save_msg = ""; if ($R::command eq "Save Closing Stock") { my @pars = $q->param; foreach (@pars) { if ($_ =~ /^(cs$tempdb{stocktype}_)(\d+)/) { $tempdb{"cs$tempdb{stocktype}_$2"}=$q->param("cs$tempdb{stocktype}_$2"); } } #let's try copy here #insert into stockissue (date,direction,stocktype,stockitems_id,qty)values (?,?,?,?,?) #direction is 2 if($q->param('copypastesave_flag') ==1){ my @val = $q->param('stockitems_id'); my $f->{stockitems_id} = @val>1 ? \@val : $val[0]; my $ids = join(',',grep{/^\d+$/}@{&ARef($f->{stockitems_id})}); if($ids){ my %sqldate = dateforsql($R::date); $results = $dbh->prepare("SELECT id, bin, units,sku ,qty FROM stockitems WHERE id in($ids)"); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { my $id = $ref->{id}; my $cs1_count = $q->param("cs$tempdb{stocktype}_$id"); $dbh->do("insert into stockissue (date,direction,stocktype,stockitems_id,qty) values (?,?,?,?,?)",undef, "$sqldate{year}-$sqldate{month}-$sqldate{day}", 2, $stocktype, $ref->{id}, $cs1_count||0 ); } $copy_save_msg = "Selected stock items have been copied."; } } } sub ARef{ my $data=shift; $data=[] unless $data; $data=[$data] unless ref($data) eq 'ARRAY'; return $data; } #/All queries for the screen #/get all stock in memory: name, bin, units my %stockdetails; $results = $dbh->prepare(" SELECT si.id,concat(sc.name,' - ',si.name) name, bin, units,sku FROM stockitems si LEFT JOIN stockcats sc ON sc.id = si.stockcats_id WHERE si.f_status<100 and si.shop_id=$shop{id} and sc.f_status<100 and sc.shop_id=$shop{id} and si.type=$stocktype; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $stockdetails{name}{$ref->{id}} = $ref->{name}; $stockdetails{bin}{$ref->{id}} = $ref->{bin}; $stockdetails{units}{$ref->{id}} = $ref->{units}; $stockdetails{sku}{$ref->{id}} = $ref->{sku}; $stockdetails{stitem_id}{$ref->{id}} = $ref->{id}; } #/determine last pid for the stocktype $results = $dbh->prepare("select last_pid,date from cstock where stocktype=$stocktype and shop_id=$shop{id} and f_status<100 order by id desc limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $last_pid = $ref->{last_pid}*1; my $last_date = $ref->{date}; $last_date = "1900-01-01" if (! $last_date); #/purchases sinse last pid excluding my %last_purchases; $results = $dbh->prepare("select si.id id, si.name name, si.units units, si.bin bin, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where si.f_status<100 and pu.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0) group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_purchases{$ref->{id}}{qty} = $ref->{qty}; $last_purchases{$ref->{id}}{value} = $ref->{totalprice}; } #/last closing stock qty and value my %last_closingstock; $results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty, sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice from cstock cs, stockitems si where cs.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and cs.shop_id=si.shop_id and si.id=cs.stockitems_id and si.type=$stocktype and cs.last_pid=$last_pid #/cs:last_pid and cs.date='$last_date' group by si.id order by si.name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_closingstock{$ref->{id}}{qty} = $ref->{qty}; $last_closingstock{$ref->{id}}{value} = $ref->{stockvalue}; } #/last credit notes - since last_pid my %last_creditnotes; $results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where pu.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and pu.cnote = 1 group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_creditnotes{$ref->{id}}{qty} = $ref->{qty}; $last_creditnotes{$ref->{id}}{value} = $ref->{totalprice}; } #/calculate the availables; my %availables; foreach my $id (keys %{$stockdetails{name}}) { my $available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty}); next if ($available_qty <= 0 and $show_noqty_stock == -1); $availables{$id} = $available_qty; } #/by bin / by name my $sortage = $orderby; #///All queries #/ #/ PRINT CURRENT CLOSING STOCK EDITOR #/ $temphtml .= $table{start}; if($copy_save_msg){ $temphtml .= qq|
" : "")."".rn3($availables{$id})." $units{$stockdetails{units}{$id}}| $units{$stockdetails{units}{$id}} |
" : "").(rn3($availables{$id} - $tempdb{"cs$tempdb{stocktype}_$id"}) )." $units{$stockdetails{units}{$id}}|; $temphtml .= " "; $temphtml .= qq~
SELECT STOCK TAKE DATE:
~;
$temphtml .= ""; $temphtml .= "
"; $temphtml .= "
"; $temphtml .= qq|
|; $temphtml .= ""; $temphtml .= "
";
$temphtml .= "";
$temphtml .= "Filter by SKU, Category or Description:
"; my $stocktype=$tempdb{stocktype}; #/All queries for the screen #/get all stock in memory: name, bin, units my %stockdetails; $results = $dbh->prepare(" SELECT si.sku,si.id,sc.name cat, si.name name, bin, units,cost FROM stockitems si LEFT JOIN stockcats sc ON sc.id = si.stockcats_id WHERE si.f_status<100 and si.shop_id=$shop{id} and sc.f_status<100 and sc.shop_id=$shop{id} and si.type=$stocktype; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $stockdetails{name}{$ref->{id}} = $ref->{name}; $stockdetails{bin}{$ref->{id}} = $ref->{bin}; $stockdetails{units}{$ref->{id}} = $ref->{units}; $stockdetails{cost}{$ref->{id}} = $ref->{cost}; $stockdetails{sku}{$ref->{id}} = $ref->{sku}; $stockdetails{cat}{$ref->{id}} = $ref->{cat} } #/determine last pid for the stocktype $results = $dbh->prepare("select last_pid,date from cstock where stocktype=$stocktype and shop_id=$shop{id} and f_status<100 order by id desc limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $last_pid = $ref->{last_pid}*1; my $last_date = $ref->{date}; #/purchases sinse last pid excluding $last_date = "1900-01-01" if (! $last_date); my %last_purchases; $results = $dbh->prepare("select si.id id, si.name name, si.units units, si.bin bin, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where si.f_status<100 and pu.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0) group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_purchases{$ref->{id}}{qty} = $ref->{qty}; $last_purchases{$ref->{id}}{value} = $ref->{totalprice}; } #/last closing stock qty and value my %last_closingstock; $results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty, sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice from cstock cs, stockitems si where cs.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and cs.shop_id=si.shop_id and si.id=cs.stockitems_id and si.type=$stocktype and cs.last_pid=$last_pid #/cs:last_pid and cs.date='$last_date' group by si.id order by si.name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_closingstock{$ref->{id}}{qty} = $ref->{qty}; $last_closingstock{$ref->{id}}{value} = $ref->{stockvalue}; } #/last credit notes - since last_pid my %last_creditnotes; $results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where pu.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and pu.cnote = 1 group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_creditnotes{$ref->{id}}{qty} = $ref->{qty}; $last_creditnotes{$ref->{id}}{value} = $ref->{totalprice}; } #/calculate the availables; my %availables = (); foreach my $id (keys %{$stockdetails{name}}) { my $available_qty; $available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty}); next if ($available_qty <= 0 and $show_noqty_stock == -1); $availables{$id} = $available_qty || 0; } #/by bin / by name my $sortage = $orderby; #///All queries $temphtml .= " LAST STOCK TAKE DATE: $last_date $stocktypes{$database}{$tempdb{stocktype}} Stock Sheet
"; $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= "
"; #THEORY #GRAND RETREIVAL #retreive ingredient details my %idetails; $results = $dbh->prepare("select id,name,stockitems_id,units from ritems;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ $idetails{$ref->{id}}{stockitems_id} = $ref->{stockitems_id}; $idetails{$ref->{id}}{units} = $ref->{units}; } my %latestyields; $results = $dbh->prepare(" select id ritem_id, yield from ritems;" ) or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $idetails{$ref->{ritem_id}}{yield} = $ref->{yield}; } my %stockuse; my %ingredientuse; my %pluuse; my %returns; $last_date = "1900-01-01" if (! $last_date); #print "'$last_date 06:00:00' ST: $tempdb{stocktype}"; #TO RETREIVE TOTAL PLU QTYs (based on QTY or YIELD column); my %onetoone; $results = $dbh->prepare(" select menu.id pluid, inv.isrefund, menu.stockid stockid, inv.voided voided, sum(qty) totalqty from tillordersdetails ttod, tillorders tto, menu, tillinvoices inv, tilltableops tabops, tilltables where ttod.f_status < 101 and tto.f_status <101 and ttod.stamp >= '$last_date 06:00:00' #and menu.type=1 and tabops.id = tto.tableops_id and tilltables.id = tabops.table_id and ttod.orders_id = tto.id and tto.confirm_status=3 and menu.type = '$tempdb{stocktype}' and ttod.menusource = 1 and menu.id = ttod.plu and inv.id = ttod.invoice and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) group by menu.id, inv.voided, inv.isrefund;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ if ( $ref->{isrefund} == 1 ) { $pluuse{$ref->{pluid}} += $ref->{totalqty}; } else { $pluuse{$ref->{pluid}} += $ref->{totalqty} if ($ref->{voided} != 2); $returns{$ref->{pluid}} += $ref->{totalqty} if ($ref->{voided} == 2); } $onetoone{$ref->{pluid}} = $ref->{stockid}; } # loop the PLUs in the following: foreach my $plu (keys %pluuse) { $results = $dbh->prepare(" SELECT rn.name name, rn.id id, mr.qty qty, ry.yield totaly from recipe_names rn, menu_recipes mr, recipe_yields ry where ry.recipe_id = rn.id and ry.yield_unit = mr.recipe_units and mr.menu_id=$plu and rn.id=mr.recipe_id ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ my %usage = &recipeingredients($ref->{id},$ref->{totaly}/$ref->{qty}, {dbh => $dbh} ); foreach my $ingred (keys %usage) { #print "|$idetails{$ingred}{stockitems_id} -- $stockuse{$idetails{$ingred}{stockitems_id}}"; # THE LiNK $stockuse{$idetails{$ingred}{stockitems_id}} += $pluuse{$plu}*$usage{$ingred}* #usage in the PLU ((100+(100-$idetails{$ingred}{yield}))/100)* #yield towards stockitem ${$unitalias{ $idetails{$ingred}{units} } }[1]; } } #deal with onetoones $stockuse{$onetoone{$plu}} += $pluuse{$plu} if {$onetoone{$plu} > 0}; } #foreach PLU #END #/ #/ PRINT CLOSING STOCK SHEET #/ $temphtml .= $table{startp}; $temphtml .= "";
$temphtml .= "
SKU CODE
CATEGORY
STOCK ITEM
PREVIOUS STOCK
PURCHASES
CREDIT NOTES
STOCK ON HAND
PLU USAGE
THEORETICAL
STOCK
VALUE
COUNT ";
$temphtml .= " ";
#/use Time::HiRes qw(gettimeofday);
#/my ($starttime, $endtime, $elapsed);
my $totalvalue = 0;
foreach my $id (
sort {$stockdetails{sku}{$a} cmp $stockdetails{sku}{$b}}
sort {$stockdetails{cat}{$a} cmp $stockdetails{cat}{$b}}
sort {$stockdetails{name}{$a} cmp $stockdetails{name}{$b}}
keys %availables)
{
if ($R::filter) {
my $filter = $R::filter;
if (
$stockdetails{sku}{$id} =~ /($filter)/i
||
$stockdetails{cat}{$id} =~ /($filter)/i
||
$stockdetails{name}{$id} =~ /($filter)/i
) {} else {
next;
}
}
$temphtml .= "";
$temphtml .= "
$stockdetails{sku}{$id}
$stockdetails{cat}{$id}
$stockdetails{name}{$id}
".($last_closingstock{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_purchases{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_creditnotes{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($availables{$id} < 0 ? "ERROR! " : "")."".pr($availables{$id})." $units{$stockdetails{units}{$id}}
".($stockuse{$id}*1)." $units{$stockdetails{units}{$id}}
".($availables{$id} - $stockuse{$id})." $units{$stockdetails{units}{$id}}
$currency ".pr( ($availables{$id} - $stockuse{$id} ) * $stockdetails{cost}{$id} )."
";
$totalvalue += ($availables{$id} - $stockuse{$id}) * $stockdetails{cost}{$id};
}
$temphtml .= " Total Value of Stock on Hand: $currency ".pr($totalvalue)."
"; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
}
elsif ($socketflag == 1 and $R::action eq "Closing Stock" or $R::action eq "Back to Closing Stock") {
my $temphtml;
my %calc;
my $sqlorder;
#/type 1 = by name; type 2=by bin
#/retreive perm stage
$tempdb{cstockviewtype} = $R::viewtype if $R::viewtype;
#/permanent stage - viewtype
$tempdb{cstockviewtype} = 1 if !$tempdb{cstockviewtype};
#/retreive permanent stages
$tempdb{stocktype} = $R::stocktype if $R::stocktype;
#/no permanent stages yet - init
$tempdb{stocktype} = (sort {$a <=> $b} keys %{ $stocktypes{$database} })[0] if !$tempdb{stocktype};
my %ordertype = (1 => 'name', 2 => 'bin');
my $orderby = $ordertype{$tempdb{cstockviewtype}};
$template =~ s/\@title\@/View Stock $cstockviewtypes{$tempdb{cstockviewtype}}/sgi;
$temphtml = ""; my $stocktype=$tempdb{stocktype}; #/All queries for the screen #/get all stock in memory: name, bin, units my %stockdetails; $results = $dbh->prepare(" SELECT si.sku,si.id,sc.name cat, si.name name, bin, units,cost FROM stockitems si LEFT JOIN stockcats sc ON sc.id = si.stockcats_id WHERE si.f_status<100 and si.shop_id=$shop{id} and sc.f_status<100 and sc.shop_id=$shop{id} and si.type=$stocktype; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $stockdetails{name}{$ref->{id}} = $ref->{name}; $stockdetails{bin}{$ref->{id}} = $ref->{bin}; $stockdetails{units}{$ref->{id}} = $ref->{units}; $stockdetails{cost}{$ref->{id}} = $ref->{cost}; $stockdetails{sku}{$ref->{id}} = $ref->{sku}; $stockdetails{cat}{$ref->{id}} = $ref->{cat} } #/determine last pid for the stocktype $results = $dbh->prepare("select last_pid,date from cstock where stocktype=$stocktype and shop_id=$shop{id} and f_status<100 order by id desc limit 1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $last_pid = $ref->{last_pid}*1; my $last_date = $ref->{date}; #/purchases sinse last pid excluding $last_date = "1900-01-01" if (! $last_date); my %last_purchases; $results = $dbh->prepare("select si.id id, si.name name, si.units units, si.bin bin, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where si.f_status<100 and pu.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0) group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_purchases{$ref->{id}}{qty} = $ref->{qty}; $last_purchases{$ref->{id}}{value} = $ref->{totalprice}; } #/last closing stock qty and value my %last_closingstock; $results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty, sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice from cstock cs, stockitems si where cs.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and cs.shop_id=si.shop_id and si.id=cs.stockitems_id and si.type=$stocktype and cs.last_pid=$last_pid #/cs:last_pid and cs.date='$last_date' group by si.id order by si.name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_closingstock{$ref->{id}}{qty} = $ref->{qty}; $last_closingstock{$ref->{id}}{value} = $ref->{stockvalue}; } #/last credit notes - since last_pid my %last_creditnotes; $results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice from purchases pu,stockitems si where pu.f_status<100 and si.f_status<100 and si.shop_id=$shop{id} and pu.shop_id=si.shop_id and si.id=pu.stockitems_id and si.type=$stocktype and pu.id>$last_pid #/cs:last_pid and pu.cnote = 1 group by si.id order by name;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $last_creditnotes{$ref->{id}}{qty} = $ref->{qty}; $last_creditnotes{$ref->{id}}{value} = $ref->{totalprice}; } #/calculate the availables; my %availables = (); foreach my $id (keys %{$stockdetails{name}}) { my $available_qty; $available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty}); next if ($available_qty <= 0 and $show_noqty_stock == -1); $availables{$id} = $available_qty || 0; } #/by bin / by name my $sortage = $orderby; #///All queries $temphtml .= " LAST STOCK TAKE DATE: $last_date $stocktypes{$database}{$tempdb{stocktype}} Stock Sheet
"; $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= "
"; #THEORY #GRAND RETREIVAL #retreive ingredient details my %idetails; $results = $dbh->prepare("select id,name,stockitems_id,units from ritems;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ $idetails{$ref->{id}}{stockitems_id} = $ref->{stockitems_id}; $idetails{$ref->{id}}{units} = $ref->{units}; } my %latestyields; $results = $dbh->prepare(" select id ritem_id, yield from ritems;" ) or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()) { $idetails{$ref->{ritem_id}}{yield} = $ref->{yield}; } my %stockuse; my %ingredientuse; my %pluuse; my %returns; $last_date = "1900-01-01" if (! $last_date); #print "'$last_date 06:00:00' ST: $tempdb{stocktype}"; #TO RETREIVE TOTAL PLU QTYs (based on QTY or YIELD column); my %onetoone; $results = $dbh->prepare(" select menu.id pluid, inv.isrefund, menu.stockid stockid, inv.voided voided, sum(qty) totalqty from tillordersdetails ttod, tillorders tto, menu, tillinvoices inv, tilltableops tabops, tilltables where ttod.f_status < 101 and tto.f_status <101 and ttod.stamp >= '$last_date 06:00:00' #and menu.type=1 and tabops.id = tto.tableops_id and tilltables.id = tabops.table_id and ttod.orders_id = tto.id and tto.confirm_status=3 and menu.type = '$tempdb{stocktype}' and ttod.menusource = 1 and menu.id = ttod.plu and inv.id = ttod.invoice and (inv.voided is NULL or inv.voided = 2 or inv.voided = 0) group by menu.id, inv.voided, inv.isrefund;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ if ( $ref->{isrefund} == 1 ) { $pluuse{$ref->{pluid}} += $ref->{totalqty}; } else { $pluuse{$ref->{pluid}} += $ref->{totalqty} if ($ref->{voided} != 2); $returns{$ref->{pluid}} += $ref->{totalqty} if ($ref->{voided} == 2); } $onetoone{$ref->{pluid}} = $ref->{stockid}; } # loop the PLUs in the following: foreach my $plu (keys %pluuse) { $results = $dbh->prepare(" SELECT rn.name name, rn.id id, mr.qty qty, ry.yield totaly from recipe_names rn, menu_recipes mr, recipe_yields ry where ry.recipe_id = rn.id and ry.yield_unit = mr.recipe_units and mr.menu_id=$plu and rn.id=mr.recipe_id ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); while ($ref = $results->fetchrow_hashref()){ my %usage = &recipeingredients($ref->{id},$ref->{totaly}/$ref->{qty}, {dbh => $dbh} ); foreach my $ingred (keys %usage) { #print "|$idetails{$ingred}{stockitems_id} -- $stockuse{$idetails{$ingred}{stockitems_id}}"; # THE LiNK $stockuse{$idetails{$ingred}{stockitems_id}} += $pluuse{$plu}*$usage{$ingred}* #usage in the PLU ((100+(100-$idetails{$ingred}{yield}))/100)* #yield towards stockitem ${$unitalias{ $idetails{$ingred}{units} } }[1]; } } #deal with onetoones $stockuse{$onetoone{$plu}} += $pluuse{$plu} if {$onetoone{$plu} > 0}; } #foreach PLU #END #/ #/ PRINT CLOSING STOCK SHEET #/ $temphtml .= $table{startp}; $temphtml .= "
STOCK
"; $temphtml .= ""; $temphtml .= "
";
my $stocktype=$tempdb{stocktype};
#/All queries for the screen
#/get all stock in memory: name, bin, units
my %stockdetails;
$results = $dbh->prepare("
SELECT si.id,concat(sc.name,' - ',si.name) name, bin, units, sku
FROM stockitems si
LEFT JOIN stockcats sc ON sc.id = si.stockcats_id
WHERE si.f_status<100 and si.shop_id=$shop{id}
and sc.f_status<100 and sc.shop_id=$shop{id}
and si.type=$stocktype;
;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$stockdetails{name}{$ref->{id}} = $ref->{name};
$stockdetails{bin}{$ref->{id}} = $ref->{bin};
$stockdetails{units}{$ref->{id}} = $ref->{units};
$stockdetails{sku}{$ref->{id}} = $ref->{sku};
}
#/determine last pid for the stocktype
$results = $dbh->prepare("select last_pid,date from cstock where stocktype=$stocktype and shop_id=$shop{id} and f_status<100 order by id desc limit 1;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $last_pid = $ref->{last_pid}*1;
my $last_date = $ref->{date};
#/purchases sinse last pid excluding
$last_date = "1900-01-01" if (! $last_date);
my %last_purchases;
$results = $dbh->prepare("select si.id id, si.name name,
si.units units, si.bin bin, sum(pu.qty) qty,
sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice
from purchases pu,stockitems si
where
si.f_status<100 and pu.f_status<100
and si.shop_id=$shop{id}
and pu.shop_id=si.shop_id
and si.id=pu.stockitems_id
and si.type=$stocktype
and pu.id>$last_pid #/cs:last_pid
and (pu.cnote != 1 or pu.cnote is NULL or pu.cnote = 0)
group by si.id
order by name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_purchases{$ref->{id}}{qty} = $ref->{qty};
$last_purchases{$ref->{id}}{value} = $ref->{totalprice};
}
#/last closing stock qty and value
my %last_closingstock;
$results = $dbh->prepare("select si.id id, si.name name, sum(cs.qty) qty,
sum(cs.value) stockvalue, sum(cs.value)/sum(cs.qty) uprice
from cstock cs, stockitems si
where
cs.f_status<100 and si.f_status<100
and si.shop_id=$shop{id}
and cs.shop_id=si.shop_id
and si.id=cs.stockitems_id
and si.type=$stocktype
and cs.last_pid=$last_pid #/cs:last_pid
and cs.date='$last_date'
group by si.id
order by si.name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_closingstock{$ref->{id}}{qty} = $ref->{qty};
$last_closingstock{$ref->{id}}{value} = $ref->{stockvalue};
}
#/last credit notes - since last_pid
my %last_creditnotes;
$results = $dbh->prepare("select si.id id, si.name name, sum(pu.qty) qty, sum(pu.org_price) totalprice, sum(pu.org_price)/sum(pu.qty) uprice
from purchases pu,stockitems si
where
pu.f_status<100 and si.f_status<100
and si.shop_id=$shop{id}
and pu.shop_id=si.shop_id
and si.id=pu.stockitems_id
and si.type=$stocktype
and pu.id>$last_pid #/cs:last_pid
and pu.cnote = 1
group by si.id
order by name;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$last_creditnotes{$ref->{id}}{qty} = $ref->{qty};
$last_creditnotes{$ref->{id}}{value} = $ref->{totalprice};
}
#/calculate the availables;
my %availables = ();
foreach my $id (keys %{$stockdetails{name}}) {
my $available_qty = $last_closingstock{$id}{qty} + ($last_purchases{$id}{qty} - $last_creditnotes{$id}{qty});
next if ($available_qty <= 0 and $show_noqty_stock == -1);
$availables{$id} = $available_qty || 0;
}
#/by bin / by name
my $sortage = $orderby;
#///All queries
$temphtml .= " $stocktypes{$database}{$tempdb{stocktype}} Stock Sheet";
$temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= " | View by name | View by bin
"; #/ #/ PRINT CLOSING STOCK SHEET #/ $temphtml .= $table{startp}; $temphtml .= "";
$temphtml .= "SKU STOCK ITEM
BIN
PREVIOUS STOCK
PURCHASES
CREDIT NOTES
STOCK ON HAND
NEW STOCK ";
$temphtml .= " ";
#/use Time::HiRes qw(gettimeofday);
#/my ($starttime, $endtime, $elapsed);
foreach my $id (
sort {$stockdetails{$sortage}{$a} cmp $stockdetails{$sortage}{$b}}
sort {$stockdetails{name}{$a} cmp $stockdetails{name}{$b}} keys %availables)
{
next if ($availables{$id} <= 0 and $show_noqty_stock == -1);
$temphtml .= "";
$temphtml .= "$stockdetails{sku}{$id}
$stockdetails{name}{$id}
$bins{$database}{$stockdetails{bin}{$id}}
".($last_closingstock{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_purchases{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($last_creditnotes{$id}{qty}*1)." $units{$stockdetails{units}{$id}}
".($availables{$id} < 0 ? "ERROR! " : "")."".pr($availables{$id})." $units{$stockdetails{units}{$id}}
$units{$stockdetails{units}{$id}} ";
$temphtml .= " ";
}
$temphtml .= $table{end};
my $warning;
if ($tempdb{stockflag} == 2) {$warning = "onClick=\"return confirm('YOU WILL NOT BE ALLOWED TO EDIT ANY MORE PURCHASES WHILE YOU ARE IN CLOSING STOCK EDITOR! ARE YOU SURE ?');\""}
$temphtml .= "
"; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end CLOSING STOCK SHEET
elsif ($socketflag == 1 and $R::action eq "Opening Stock") { #/TODO - eventual combine with purchases and remove this action
#/$R::lastpid=11875; #/***BIBI***
my $temphtml;
my ($purtype);
my $ostocklimit;
$purtype=1; #/ opening stock
$onload="document.form1.stockitems_id.focus();";
#/retreive permanent stages
$tempdb{ostocktype} = $R::ostocktype if ($R::ostocktype);
#/no permanent stages yet - init
if (! $tempdb{ostocktype}) {$tempdb{ostocktype}=1};
if ($tempdb{ostocktype} == 2){
$template =~ s/\@title\@/Edit Opening Stock - Liqour/sgi;
$ostocklimit = "and si.type=2 and si.stockitem <> 2";
} else {
$template =~ s/\@title\@/Edit Opening Stock - Food/sgi;
$ostocklimit = "and si.type=1 and si.stockitem <> 2";
}
$R::lastpid=0 if (! $R::lastpid);
$temphtml = ""; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= " | View by name | View by bin
"; #/ #/ PRINT CLOSING STOCK SHEET #/ $temphtml .= $table{startp}; $temphtml .= "
$units{$stockdetails{units}{$id}}"; $temphtml .= ""; $temphtml .= "
PASSWORD:
last PID: ";
if ($R::passwd eq "obi4amte") {
#/## OSTOCK EDIT COMMANDS
if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) {
my ($new_qty, $new_price);
#/$new_qty = $R::org_qty*${unitalias{$R::org_units}}[1];
#/$new_price = pr($R::org_price/$new_qty);
$new_qty=$R::org_qty;
$new_price=$new_qty*$R::org_price;
my $results = $dbh->prepare("INSERT INTO purchases
(shop_id, stockitems_id, org_qty, org_price, org_units, qty, price, date, type, cnote) values
($shop{id}, $R::stockitems_id, $R::org_qty, $new_price, $R::org_units, $new_qty, $R::org_price,CURRENT_DATE(),$purtype,0);") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "DATA ADDED.";
} elsif ($R::command eq "delete") {
my $results = $dbh->prepare("UPDATE purchases SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "STOCK DELETED.";
} elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) {
my %sqldate = dateforsql($R::date);
my ($new_qty, $new_price);
#/$new_qty = $R::org_qty*${unitalias{$R::org_units}}[1];
#/$new_price = pr($R::org_price/$new_qty);
$new_qty=$R::org_qty;
$new_price=$new_qty*$R::org_price;
my $results = $dbh->prepare("UPDATE purchases SET
stockitems_id='$R::stockitems_id',
supplier_id='$R::supplier_id',
org_qty='$R::org_qty',
org_price='$new_price',
qty='$new_qty',
price='$R::org_price',
invoice='$R::invoice',
description='$R::description',
cnote=0,
f_status=1
WHERE id=$R::itemid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$mess = "DATA MODIFIED.";
}
#/## /COMMANDS
$temphtml .= "
"; $temphtml .= "Food Opening Stock | Liqour Opening Stock"; my $results = $dbh->prepare("select sum(pu.org_price) total from purchases pu, stockitems si where si.stockitem <> 2 and pu.stockitems_id=si.id and pu.id>=$R::lastpid and pu.shop_id=$shop{id} and pu.f_status<100 and si.shop_id=$shop{id} and si.f_status<100 $ostocklimit and pu.type=1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); $temphtml .= " | VALUE TOTAL: $currency".pr($ref->{total}); $temphtml .= "
"; #/ DRAW TABLE OPURCH $temphtml .= $table{start}; $temphtml .= "";
$temphtml .= "
Item
Stock Qty
Stock Units
Stock Price
Qty
Units
Price
Value
:COMMANDS: ";
$temphtml .= " ";
$results = $dbh->prepare("
SELECT pu.id id, pu.qty qty, pu.org_price org_price, pu.price price, price*qty value,
pu.org_qty org_qty, pu.org_units org_units,
si.type type,
pu.stockitems_id stockitems_id,
si.name item, si.units units, si.type stocktype
FROM purchases pu, stockitems si
WHERE pu.shop_id =$shop{id} and pu.f_status<100
and pu.id>=$R::lastpid
and si.id=pu.stockitems_id and pu.type=1
and si.stockitem <> 2
$ostocklimit
ORDER BY si.name ;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$temphtml .= "";
$temphtml .= "
{stocktype}}>$ref->{item}
$ref->{org_qty}
${ $unitalias{$ref->{org_units}} }[0]
$ref->{org_price}
$ref->{qty}
$units{$ref->{units}}
$ref->{price}
$ref->{value}
";
$temphtml .= "";
$temphtml .= "{id}\" onclick=\"return confirm('DELETE ITEM: $ref->{item} ?');\">$icons{d}";
$temphtml .= " {id}\">$icons{m}";
$temphtml .= " ";
$temphtml .= " ";
} else {
$temphtml .= "";
$temphtml .= "
";
$temphtml .="
{org_qty}\" $calt_e3>
*
";
#/";
$temphtml .="
* * * {price}\" $calt_e> *
";
}
}
if ($R::command ne "modify" ) {
$temphtml .= "";
$temphtml .= "
";
$temphtml .="
*
";
#/";
$temphtml .="
* * * *
";
}
$temphtml .= qq ~
"; } #/if passwd $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} elsif ($socketflag == 1 and $R::action eq "Opening Stock" and $tempdb{stockflag} != 1) {
$template =~ s/\@title\@/Error/sgi;
my $temphtml = ""; $temphtml .= "Food Opening Stock | Liqour Opening Stock"; my $results = $dbh->prepare("select sum(pu.org_price) total from purchases pu, stockitems si where si.stockitem <> 2 and pu.stockitems_id=si.id and pu.id>=$R::lastpid and pu.shop_id=$shop{id} and pu.f_status<100 and si.shop_id=$shop{id} and si.f_status<100 $ostocklimit and pu.type=1;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); $temphtml .= " | VALUE TOTAL: $currency".pr($ref->{total}); $temphtml .= "
"; #/ DRAW TABLE OPURCH $temphtml .= $table{start}; $temphtml .= "
"; } #/if passwd $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
Function disabled! You must finish editing your Closing Stock in order to use this function"; $template =~ s/\@content\@/$temphtml/sgi; }#/ end OPENING STOCK editor elsif ($socketflag == 1 and $R::action eq "creditors") { my $temphtml; my $lockflag=1; my $mydate; my $defaultdate; $onload="document.form1.amount.focus();"; $defaultdate = datefromsql($R::pdate) || $date; #/$onload="document.form1.stockitems_id.focus();"; $template =~ s/\@title\@/Edit Creditors/sgi; $temphtml = "
";
if ($R::petty) {
$subresults = $dbh->prepare("SELECT f_lock,DATE_FORMAT(date, '%e %M %Y') mydate from dcash where f_status<100 and shop_id=$shop{id} and date='$R::pdate';") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$subresults->finish();
$lockflag=$subref->{f_lock};
$mydate=$subref->{mydate};
$temphtml .= "PETTY CASH - $mydate
"; } #/## CREDITOR EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { my $supplier_id; my $wholesaler_id; $supplier_id = undef; $wholesaler_id = undef; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my $results = $dbh->prepare("INSERT INTO creditors (shop_id,amount,date,supplier_id,description) values (?,?,?,?,?);") or die $dbh->errstr(); $results->execute($shop{id},$R::amount,$R::pdate,$supplier_id,$R::description) or die $results->errstr(); $mess = "DATA ADDED."; } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE creditors SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "RECORD DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my $supplier_id; my $wholesaler_id; $supplier_id = undef; $wholesaler_id = undef; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my $results = $dbh->prepare("UPDATE creditors SET shop_id=?,amount=?,date=?,supplier_id=?,description=?, f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($shop{id},$R::amount,$R::pdate,$supplier_id,$R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE CREDITORS $temphtml .= $table{start}; $temphtml .= "";
$temphtml .= "
Amount
Supplier
Description
";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
$results = $dbh->prepare("SELECT * from creditors where date='$R::pdate' and shop_id=$shop{id} and f_status<100;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$temphtml .= "";
if ($ref->{supplier_id}) {
$subresults = $dbh->prepare("SELECT name from suppliers where id=$ref->{supplier_id};") or die $dbh->errstr();
} else{
$subresults = $dbh->prepare("SELECT name from wholesalers where id=$ref->{wholesaler_id};") or die $dbh->errstr();
}
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$temphtml .= "$ref->{amount} $subref->{name} $ref->{description} ";
$temphtml .= "
{id}&pdate=$R::pdate&dcashid=$R::dcashid\" onclick=\"return confirm('DELETE ITEM: $ref->{item} ?');\">$icons{d}
{id}&pdate=$R::pdate&dcashid=$R::dcashid\">$icons{m}
" if ($lockflag ==1);
$temphtml .= " ";
} else {
my %ssel;
if (! $ref->{supplier_id})
{ $ssel{"w".$ref->{wholesaler_id}}="SELECTED" }
else { $ssel{"s".$ref->{supplier_id}}="SELECTED" }
$temphtml .= "";
$temphtml .= "
$currency {amount}\"> {description}\">
";
$temphtml .= " ";
}
}
if ($R::petty) {
#/## TOTALS PETTYCASH
my %calc;
my $results = $dbh->prepare("SELECT SUM(org_price) FROM purchases WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate' and type=3;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} = $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM loans WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM creditors WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
#/comparation/substraction
$results = $dbh->prepare("SELECT pettycash FROM dcash WHERE shop_id=$shop{id} and f_status<100 and id=$R::dcashid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{diff} = $calc{amount} - $ref;
$calc{aspercash} = $ref;
if (sprintf("%.2f",$calc{diff}) == 0) {$linecolor=""} else {$linecolor=$errorcolor}
$temphtml .= "
Total as per Petty Cash ".pr($calc{amount})."
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
";
$temphtml .= "
$currency ";
}
$temphtml .= qq ~
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/creditors
elsif ($socketflag == 1 and $R::action eq "loans") {
my $temphtml;
my $lockflag=1;
my $mydate;
my $defaultdate;
$onload="document.form1.amount.focus();";
my (%waitrons, %allwaitrons);
#/ prepare waitrons
my $subresults = $dbh->prepare("SELECT id, name,active FROM waitron WHERE f_status<100 and shop_id=$shop{id} ORDER BY name;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
while ($subref = $subresults->fetchrow_hashref()) {
$allwaitrons{$subref->{id}} = $subref->{name};
$waitrons{$subref->{id}} = $subref->{name} if ($subref->{active} == 1);
}
$defaultdate = datefromsql($R::pdate) || $date;
#/$onload="document.form1.stockitems_id.focus();";
$template =~ s/\@title\@/Edit Wages\/Loans/sgi;
$temphtml = ""; } #/## CREDITOR EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { my $supplier_id; my $wholesaler_id; $supplier_id = undef; $wholesaler_id = undef; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my $results = $dbh->prepare("INSERT INTO creditors (shop_id,amount,date,supplier_id,description) values (?,?,?,?,?);") or die $dbh->errstr(); $results->execute($shop{id},$R::amount,$R::pdate,$supplier_id,$R::description) or die $results->errstr(); $mess = "DATA ADDED."; } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE creditors SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "RECORD DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my $supplier_id; my $wholesaler_id; $supplier_id = undef; $wholesaler_id = undef; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my $results = $dbh->prepare("UPDATE creditors SET shop_id=?,amount=?,date=?,supplier_id=?,description=?, f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($shop{id},$R::amount,$R::pdate,$supplier_id,$R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE CREDITORS $temphtml .= $table{start}; $temphtml .= "
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
if ($R::petty) {
$subresults = $dbh->prepare("SELECT f_lock,DATE_FORMAT(date, '%e %M %Y') mydate from dcash where f_status<100 and shop_id=$shop{id} and date='$R::pdate';") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$subresults->finish();
$lockflag=$subref->{f_lock};
$mydate=$subref->{mydate};
$temphtml .= "PETTY CASH - $mydate
"; } #/## LOAN EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { my ($waitron_id,$wageperson,$wagetype); if ($R::type == 1) {$waitron_id=$R::waitron; $wageperson="NULL"; $wagetype="NULL"} else {$waitron_id = "NULL";$wageperson="'$R::person'"; $wagetype="'$R::wagetype'"} my $results = $dbh->prepare("INSERT INTO loans (shop_id,amount,date,loantype,waitron_id,wageperson,wagetype,description) values ($shop{id},'$R::amount','$R::pdate', '$R::type', ".$waitron_id.",".$wageperson.",".$wagetype.",?);") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA ADDED."; } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE loans SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "RECORD DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my ($waitron_id,$wageperson,$wagetype); if ($R::type == 1) {$waitron_id=$R::waitron; $wageperson="NULL"; $wagetype="NULL"} else {$waitron_id = "NULL";$wageperson="'$R::person'"; $wagetype="$R::wagetype"} my $results = $dbh->prepare("UPDATE loans SET amount='$R::amount', loantype='$R::type', waitron_id=".$waitron_id.", wageperson=".$wageperson.", wagetype=".$wagetype.", description=?, f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE LOANS $temphtml .= $table{start}; $temphtml .= "";
$temphtml .= "
Amount
Person
Description
";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
$results = $dbh->prepare("SELECT * from loans where date='$R::pdate' and shop_id=$shop{id} and f_status<100;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
$temphtml .= "";
my $name;
if ($ref->{loantype} == 1) {
$name = $allwaitrons{$ref->{waitron_id}};
$name .= "*" if (! exists $waitrons{$ref->{waitron_id}});
$name .= " (waitron)";
} else {$name=$ref->{wageperson}." (other) [$wagetypes{$ref->{wagetype}}]"}
$temphtml .= "$ref->{amount} $name $ref->{description} ";
$temphtml .= "
{id}&pdate=$R::pdate&dcashid=$R::dcashid\" onclick=\"return confirm('DELETE ITEM: $ref->{item} ?');\">$icons{d}
{id}&pdate=$R::pdate&dcashid=$R::dcashid\">$icons{m}
" if ($lockflag ==1);
$temphtml .= " ";
} else {
my (%waitronsel, $name, %typesel);
if ($ref->{loantype} == 1) {
$waitronsel{$ref->{waitron_id}} = "selected";
$typesel{1}="checked";
} else {$name=$ref->{wageperson};$typesel{2}="checked";}
$temphtml .= "";
$temphtml .= "
$currency {amount}>
{description}\">
";
$temphtml .= " ";
}
}
if ($R::petty) {
#/## TOTALS PETTYCASH
my %calc;
my $results = $dbh->prepare("SELECT SUM(org_price) FROM purchases WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate' and type=3;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} = $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM loans WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM creditors WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
#/comparation/substraction
$results = $dbh->prepare("SELECT pettycash FROM dcash WHERE shop_id=$shop{id} and f_status<100 and id=$R::dcashid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{diff} = $calc{amount} - $ref;
$calc{aspercash} = $ref;
if (sprintf("%.2f",$calc{diff}) == 0) {$linecolor=""} else {$linecolor=$errorcolor}
$temphtml .= "
Total as per Petty Cash ".pr($calc{amount})."
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
";
$temphtml .= "
$currency
";
}
$temphtml .= qq ~
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end LOANS editor
elsif ($socketflag == 1 and $R::action eq "Purchases") {
if ($R::purdayslimit) {
$tempdb{purdayslimit} = $R::purdayslimit;
}
$tempdb{purdayslimit} = 14 if (! $tempdb{purdayslimit});
my $temphtml;
$R::ptype = (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes{$database} } )[0] if ((! $R::ptype) and (! $tempdb{stocktype}));
$tempdb{stocktype} = $R::ptype if ($R::ptype);
$R::ptype = $tempdb{stocktype};
my $lastinvoicenumber = undef;
my $lastsupplier = undef;
my $lastsuppliername = undef;
my $totalforlastinvoice = undef;
my $lastsupname;
$temphtml .= qq~
~;
my ($sttype,$purtype,$last_cspid,$purlimit);
my $lockflag=1;
my $mydate;
my $defaultdate;
my $purtotalslimit;
$purtotalslimit = "and pu.invoice in ($R::purtotalslimit)" if ($R::purtotalslimit ne "");
$onload="document.form1.stockitems_id.focus();";
$purtype = 2; #/default no petty cash
my $clstocktype;
$clstocktype = $R::ptype;
$subresults = $dbh->prepare("SELECT last_pid last_pid,date from cstock
where stocktype=$clstocktype and
f_status<100 and shop_id=$shop{id} order by id desc limit 1;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$subresults->finish();
my $last_date = $subref->{date};
$last_date = "1900-01-01" if (! $last_date);
$last_cspid = $subref->{last_pid};
my %last_qty;
$subresults = $dbh->prepare("select stockitems_id,qty from cstock where date='$last_date' and f_status<100 and shop_id=$shop{id};") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
while ($ref = $subresults->fetchrow_hashref()) {
$last_qty{$ref->{stockitems_id}} = $ref->{qty};
}
$temphtml .= ""; } #/## LOAN EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { my ($waitron_id,$wageperson,$wagetype); if ($R::type == 1) {$waitron_id=$R::waitron; $wageperson="NULL"; $wagetype="NULL"} else {$waitron_id = "NULL";$wageperson="'$R::person'"; $wagetype="'$R::wagetype'"} my $results = $dbh->prepare("INSERT INTO loans (shop_id,amount,date,loantype,waitron_id,wageperson,wagetype,description) values ($shop{id},'$R::amount','$R::pdate', '$R::type', ".$waitron_id.",".$wageperson.",".$wagetype.",?);") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA ADDED."; } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE loans SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "RECORD DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { my ($waitron_id,$wageperson,$wagetype); if ($R::type == 1) {$waitron_id=$R::waitron; $wageperson="NULL"; $wagetype="NULL"} else {$waitron_id = "NULL";$wageperson="'$R::person'"; $wagetype="$R::wagetype"} my $results = $dbh->prepare("UPDATE loans SET amount='$R::amount', loantype='$R::type', waitron_id=".$waitron_id.", wageperson=".$wageperson.", wagetype=".$wagetype.", description=?, f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE LOANS $temphtml .= $table{start}; $temphtml .= "
| User | |
| Other $listhtml |
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
| User | |
| Other $listhtml |
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
#/calendar JS
$temphtml .= qq~
~;
$sttype="and si.type = $R::ptype";
$purlimit = "and pu.id>'$last_cspid'";
$template =~ s/\@title\@/Edit $stocktypes{$database}{$R::ptype} Purchases/sgi;
my %stockitem_names;
my %stockitem_warnperc;
$results = $dbh->prepare("
select concat(sc.name,' - ',si.name) name, si.id id, si.units units, si.warnperc
from stockitems si
LEFT JOIN stockcats sc ON sc.id = si.stockcats_id
where si.shop_id=$shop{id} and sc.shop_id=si.shop_id
and si.f_status<100 and sc.f_status<100
and si.type=$R::ptype
and si.stockitem != 2
and si.name != ''
;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while($ref = $results->fetchrow_hashref()) {
$stockitem_names{$ref->{id}} = $ref->{name}.", ".$units{$ref->{units}};
$stockitem_warnperc{$ref->{id}} = $ref->{warnperc};
}
if ($R::petty) {
$subresults = $dbh->prepare("SELECT f_lock,DATE_FORMAT(date, '%e %M %Y') mydate from dcash where f_status<100 and shop_id=$shop{id} and date='$R::pdate';") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$subresults->finish();
$lockflag=$subref->{f_lock};
$mydate=$subref->{mydate};
$purlimit = "and pu.date='$R::pdate'"; #/ override after last closing stock
$purtype = 3;
$temphtml .= "PETTY CASH - $mydate
"; } #/qty = org_qty * inv_units_alias_koef #/price = org_price / qty #/value=qty * price #/## PURCH EDIT COMMANDS if (($R::command eq "Add Data" and $R::supplier_id)) { $tempdb{ssc_pur_supplier_id} = $R::supplier_id; #/$tempdb{ssc_pur_stockitems_id} = $R::stockitems_id; $tempdb{ssc_pur_invoice} = $R::invoice; $tempdb{ssc_pur_date} = $R::date; $tempdb{ssc_pur_description} = $R::description; my %sqldate = dateforsql($R::date); $R::org_price *= 1; #/evaluate $R::org_qty *= 1; #/evaluate my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/notused #/debug $temphtml .= "ORG_PRICE = $R::org_price"; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty ==1 and $R::vat==1) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount>0) {$new_price = $new_price - $new_price*$R::discount/100} #$temphtml .= "WARN % $stockitem_warnperc{$R::stockitems_id}"; #warning system if ($stockitem_warnperc{$R::stockitems_id} > 0) { $results = $dbh->prepare(" select price,date,invoice from purchases where stockitems_id=$R::stockitems_id and shop_id=$shop{id} and f_status < 100 order by id desc limit 1; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{price}) { my $variance = pr ( 100 * ($new_price - $ref->{price})/$ref->{price} ); $variance *= -1 if ($variance < 0); #print "% $variance %"; if ($variance >= $stockitem_warnperc{$R::stockitems_id}) { $onload .= "alert('UNIT PRICE WARNING !!! Price variance more than $stockitem_warnperc{$R::stockitems_id} %: Last Price $currency ".pr($ref->{price})." from $ref->{date} INV: $ref->{invoice}, this price: $currency ".pr($new_price).", Variance $variance %."."');"; } } } # if variance perc defined my $results = $dbh->prepare("INSERT INTO purchases (othertype, shop_id, stockitems_id, supplier_id, org_qty, org_price, org_units, qty, price, discount, invoice, description,date,type,cnote,vat) values ('$R::othertype', $shop{id}, $R::stockitems_id, $R::supplier_id, $R::org_qty, $R::org_price, $R::org_units, $new_qty, $new_price, '$R::discount', '$R::invoice', ?, '$sqldate{year}-$sqldate{month}-$sqldate{day}',$purtype, '$R::cnote', '$R::vat');") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA ADDED."; #is this latest price ? $results = $dbh->prepare("select count(id) notlast from purchases where date > '$sqldate{year}-$sqldate{month}-$sqldate{day}' and shop_id=$shop{id} and f_status<100 and stockitems_id=$R::stockitems_id") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if (! $ref->{notlast}) { #retreive links $results = $dbh->prepare("select distinct id from ritems where stockitems_id=$R::stockitems_id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); #update unit prices - ingredients; my %linkedritems; while ($ref = $results->fetchrow_hashref()) { $linkedritems{$ref->{id}} = 1; } foreach my $ritem_id (keys %linkedritems) { $results = $dbh->prepare("select id from ritem_prices where item_id='$ritem_id' order by id desc limit 1") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $id = $ref->{id}; if ($id) { # update existing price $results = $dbh->prepare("update ritem_prices set price='$new_price', supplier_id='$R::supplier_id', date='$sqldate{year}-$sqldate{month}-$sqldate{day}' where id='$id';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } else { #create new price record $results = $dbh->prepare("insert into ritem_prices (price,date,supplier_id,item_id) values ($new_price,'$sqldate{year}-$sqldate{month}-$sqldate{day}','$R::supplier_id','$ritem_id');") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } #$supplier_id } } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE purchases SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "STOCK DELETED."; } elsif (($R::command eq "Modify Data" and $R::org_price > 0 and $R::supplier_id>0)) { my %sqldate = dateforsql($R::date); $R::org_price *= 1; #/evaluate $R::org_qty *= 1; #/evaluate my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/notused #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty==1 and $R::vat==1) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount>0) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("UPDATE purchases SET othertype='$R::othertype', stockitems_id=$R::stockitems_id, supplier_id=$R::supplier_id, org_qty=$R::org_qty, org_price=$R::org_price, org_units=$R::org_units, qty=$new_qty, price=$new_price, discount='$R::discount', invoice='$R::invoice', description=?, date='$sqldate{year}-$sqldate{month}-$sqldate{day}', cnote='$R::cnote', vat='$R::vat', f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; #is this latest price ? $results = $dbh->prepare("select count(id) notlast from purchases where date > '$sqldate{year}-$sqldate{month}-$sqldate{day}' and shop_id=$shop{id} and f_status<100 and stockitems_id=$R::stockitems_id") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if (! $ref->{notlast}) { #retreive links $results = $dbh->prepare("select distinct id from ritems where stockitems_id=$R::stockitems_id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); #update unit prices - ingredients; my %linkedritems; while ($ref = $results->fetchrow_hashref()) { $linkedritems{$ref->{id}} = 1; } foreach my $ritem_id (keys %linkedritems) { $results = $dbh->prepare("select id from ritem_prices where item_id='$ritem_id' order by id desc limit 1") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $id = $ref->{id}; if ($id) { # update existing price $results = $dbh->prepare("update ritem_prices set price='$new_price', supplier_id='$R::supplier_id', date='$sqldate{year}-$sqldate{month}-$sqldate{day}' where id='$id';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } else { #create new price record $results = $dbh->prepare("insert into ritem_prices (price,date,supplier_id,item_id) values ($new_price,'$sqldate{year}-$sqldate{month}-$sqldate{day}','$R::supplier_id','$ritem_id');") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } #$supplier_id } } } #/## /COMMANDS $defaultdate = datefromsql($R::pdate) || $tempdb{ssc_pur_date} || $date; #/ SPLITS #/quick mode if ($R::command eq "Turn Quick Mode ON") { $tempdb{pqm} = 2; } elsif ($R::command eq "Turn Quick Mode OFF") { $tempdb{pqm} = 1; } $tempdb{pqm} = 2 if (! $tempdb{pqm}); my $qm = $tempdb{pqm}; $temphtml .= "Limiting purchases for days of last invoice date.
" if (! $R::petty); $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE PURCH $temphtml .= $table{start}; $temphtml .= "";
$temphtml .= "
Item
Supplier
CN
TAX
Invoice Qty
Invoice Units
Invoice Price ";
$temphtml .= "Discount (%)
Qty
Units
U.Price
Value " if ($qm != 2);
$temphtml .= "Invoice ";
$temphtml .= "description " if ($qm != 2);
$temphtml .= "date ";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
#purchases will only show for two weeks - must be an override to this limitation
my $purdatelimit = "";
#show last purchases condition
if (! $R::petty) {
$results = $dbh->prepare("select max(date) maxdate from purchases where f_status < 100 and shop_id=$shop{id};
;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = $results->fetchrow_hashref();
my $lastexdate = $ref->{maxdate};
$purdatelimit = "and date <= '$lastexdate' and date >= DATE_SUB('$lastexdate',INTERVAL ".($tempdb{purdayslimit})." DAY)";
}
#supplier names
my %suppliers;
$results = $dbh->prepare("select name,id from suppliers
where shop_id=$shop{id} and f_status<100;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
while ($ref = $results->fetchrow_hashref()) {
$suppliers{$ref->{id}} = $ref->{name};
}
$results = $dbh->prepare("
SELECT pu.id id,
pu.qty qty,
pu.org_price org_price,
pu.price nrprice,
round(pu.price,2) price,
round(price*pu.qty,2) value,
pu.org_qty org_qty,
pu.org_units org_units,
pu.discount discount,
pu.othertype othertype,
pu.invoice invoice,
pu.description description,
DAYOFMONTH(pu.date) day,
MONTH(pu.date) month,
YEAR(pu.date) year,
pu.date date,
pu.stockitems_id stockitems_id,
pu.supplier_id supplier_id,
pu.cnote cnote, pu.vat vat,
si.units units,
si.type stocktype,
si.units siunits,
si.id sid
FROM purchases pu, stockitems si
WHERE
pu.shop_id =$shop{id} and pu.f_status<100
$purdatelimit
and si.shop_id =$shop{id} and si.f_status<100
and si.id=pu.stockitems_id
and pu.type=$purtype
$sttype
$purlimit
$purtotalslimit
ORDER BY pu.date DESC, pu.invoice, pu.id;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
#/debug print $results->rows;
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
#/8->3
if (defined $lastinvoicenumber and ($lastinvoicenumber ne $ref->{invoice})) {
$temphtml .= "
$lastsupname
Total:
$currency".pr($totalforlastinvoice)."
$lastinvoicenumber
View this invoice only
";
$totalforlastinvoice = 0;
}
$totalforlastinvoice += $ref->{nrprice}*$ref->{qty};
$lastinvoicenumber = $ref->{invoice};
$lastsupplier = $ref->{supplier_id};
$lastsuppliername = $suppliers{$ref->{supplier_id}};
$lastsupname=$suppliers{$ref->{supplier_id}};
next if ($R::totalsonly);
$temphtml .= "";
$temphtml .= "
{id}&pdate=$R::pdate&dcashid=$R::dcashid\">$stockitem_names{$ref->{sid}}";
if ($stocktypes_account_type{$database}{$tempdb{stocktype}} == 2) {
$subresults = $dbh->prepare("select id,name,type,vtype,parent,grp from bufc
where
grp <> 1
and shop_id=$shop{id}
and id = $ref->{othertype}
and f_status < 100;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$temphtml .= "
Allocation: $subref->{name}"; } $temphtml .=" ";
$temphtml .= "
$suppliers{$ref->{supplier_id}}
$cnote{$ref->{cnote}}
$cnote{$ref->{vat}}
$ref->{org_qty}
$units{$ref->{units}}
$ref->{org_price} ";
$temphtml .= "$ref->{discount}%
$ref->{qty}
$units{$ref->{units}}
$ref->{price}
$ref->{value} " if ($qm != 2);
$temphtml .= "$ref->{invoice} ";
$temphtml .= "$ref->{description} " if ($qm != 2);
$temphtml .= "$ref->{day}-$ref->{month}-$ref->{year}
";
$temphtml .= "
{item}\" href=\"index.cgi?purtotalslimit=$R::purtotalslimit&action=$R::action&subaction=$R::subaction&ptype=$R::ptype&petty=$R::petty&command=delete&itemid=$ref->{id}&pdate=$R::pdate&dcashid=$R::dcashid\" onclick=\"return confirm('DELETE ITEM: $ref->{item} ?');\">$icons{d}
{item}\" href=\"index.cgi?purtotalslimit=$R::purtotalslimit&action=$R::action&subaction=$R::subaction&ptype=$R::ptype&petty=$R::petty&command=modify&itemid=$ref->{id}&pdate=$R::pdate&dcashid=$R::dcashid\">$icons{m}
" if ($lockflag ==1);
$temphtml .= " ";
} else { #/ modify
$temphtml .= "";
$temphtml .= "
Item
Supplier
CN
TAX
Invoice Qty
Invoice Units
Invoice Price ";
$temphtml .= "Discount (%)
Qty
Units
U.Price
Value " if ($qm != 2);
$temphtml .= "Invoice ";
$temphtml .= "description " if ($qm != 2);
$temphtml .= "date ";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
$temphtml .= "";
$temphtml .= "
$icons{det}
quickfind»
find next»
"; if ($stocktypes_account_type{$database}{$tempdb{stocktype}} == 2) { $temphtml .= "
Expence Category: "; my %otsel; $otsel{$ref->{othertype}} = "selected"; my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES"); my %parentnames; my %childrenparents; my %childrennames; my %standalones; $subresults = $dbh->prepare(" select id,name,type,vtype,parent,grp from bufc where grp <> 1 and shop_id=$shop{id} and year=$budgetyear and f_status < 100;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { if (! $subref->{parent} and $subref->{vtype} == 3) { $parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) { $standalones{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif ($subref->{type} == 1) { push @{$childrenparents{$subref->{parent}}},$subref->{id}; $childrennames { $subref->{id} } = $subref->{name}; } } $temphtml .="quickfind» find next»
"; } $temphtml .=" ";
$temphtml .="
{org_qty}\" $calt_e3>
*
";
$temphtml .=" {org_price}\" $calt_e> ";
$temphtml .= " {discount}\" $calt_e>
* * * * " if ($qm != 2);
$temphtml .= " {invoice}\" $calt> ";
$temphtml .= " {description}\" $calt> " if ($qm != 2);
$temphtml .= " {day}-$ref->{month}-$ref->{year}\" $calt>$icons{cal}
";
}
}
$temphtml .= "
$lastsupname
Total:
$currency".pr($totalforlastinvoice)."
$lastinvoicenumber
View this invoice only
";
if ($R::petty) {
#/## TOTALS PETTYCASH
my %calc;
my $results = $dbh->prepare("SELECT SUM(org_price) FROM purchases WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate' and type=3;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} = $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM loans WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM creditors WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
#/comparation/substraction
$results = $dbh->prepare("SELECT pettycash FROM dcash WHERE shop_id=$shop{id} and f_status<100 and id=$R::dcashid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{diff} = $calc{amount} - $ref;
$calc{aspercash} = $ref;
if (sprintf("%.2f",$calc{diff}) == 0) {$linecolor=""} else {$linecolor=$errorcolor}
$temphtml .= "
Total as per Petty Cash ".pr($calc{amount})."
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary ";
$temphtml .= "
Item
Supplier
CN
TAX
Invoice Qty
Invoice Units
Invoice Price ";
$temphtml .= "Discount (%)
Qty
Units
U.Price
Value " if ($qm != 2);
$temphtml .= "Invoice ";
$temphtml .= "description " if ($qm != 2);
$temphtml .= "date ";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
$temphtml .= "";
$temphtml .= "
$icons{det}
quickfind»
find next»
"; if ($stocktypes_account_type{$database}{$tempdb{stocktype}} == 2) { $temphtml .= "
Expence Category: "; my %otsel; #$otsel{$ref->{othertype}} = "selected"; my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES"); my %parentnames; my %childrenparents; my %childrennames; my %standalones; $subresults = $dbh->prepare("select id,name,type,vtype,parent,grp from bufc where grp <> 1 and shop_id=$shop{id} and year=$budgetyear and f_status < 100;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { if (! $subref->{parent} and $subref->{vtype} == 3) { $parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) { $standalones{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif ($subref->{type} == 1) { push @{$childrenparents{$subref->{parent}}},$subref->{id}; $childrennames { $subref->{id} } = $subref->{name}; } } $temphtml .="quickfind» find next»
"; } $temphtml .=" ";
$temphtml .= "
*
";
#/";
$temphtml .=" ";
$temphtml .= "
* * * * " if ($qm != 2);
$temphtml .= " ";
$temphtml .= " " if ($qm != 2);
$temphtml .= " $icons{cal}
";
}
$temphtml .= qq ~
"; if ($tempdb{pqm} == 1) { $temphtml .= " | "; } elsif ($tempdb{pqm} == 2) { $temphtml .= "Quick Mode ACTIVE | "; } $temphtml .= "View invoice number: "; $temphtml .= ""; $temphtml .= " or View invoice totals."; if ($R::petty) { $temphtml .= "
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} elsif ($socketflag == 1 and $R::action eq "Purchases" and $tempdb{stockflag} != 2) {
$template =~ s/\@title\@/Error/sgi;
my $temphtml = ""; } #/qty = org_qty * inv_units_alias_koef #/price = org_price / qty #/value=qty * price #/## PURCH EDIT COMMANDS if (($R::command eq "Add Data" and $R::supplier_id)) { $tempdb{ssc_pur_supplier_id} = $R::supplier_id; #/$tempdb{ssc_pur_stockitems_id} = $R::stockitems_id; $tempdb{ssc_pur_invoice} = $R::invoice; $tempdb{ssc_pur_date} = $R::date; $tempdb{ssc_pur_description} = $R::description; my %sqldate = dateforsql($R::date); $R::org_price *= 1; #/evaluate $R::org_qty *= 1; #/evaluate my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/notused #/debug $temphtml .= "ORG_PRICE = $R::org_price"; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty ==1 and $R::vat==1) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount>0) {$new_price = $new_price - $new_price*$R::discount/100} #$temphtml .= "WARN % $stockitem_warnperc{$R::stockitems_id}"; #warning system if ($stockitem_warnperc{$R::stockitems_id} > 0) { $results = $dbh->prepare(" select price,date,invoice from purchases where stockitems_id=$R::stockitems_id and shop_id=$shop{id} and f_status < 100 order by id desc limit 1; ;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if ($ref->{price}) { my $variance = pr ( 100 * ($new_price - $ref->{price})/$ref->{price} ); $variance *= -1 if ($variance < 0); #print "% $variance %"; if ($variance >= $stockitem_warnperc{$R::stockitems_id}) { $onload .= "alert('UNIT PRICE WARNING !!! Price variance more than $stockitem_warnperc{$R::stockitems_id} %: Last Price $currency ".pr($ref->{price})." from $ref->{date} INV: $ref->{invoice}, this price: $currency ".pr($new_price).", Variance $variance %."."');"; } } } # if variance perc defined my $results = $dbh->prepare("INSERT INTO purchases (othertype, shop_id, stockitems_id, supplier_id, org_qty, org_price, org_units, qty, price, discount, invoice, description,date,type,cnote,vat) values ('$R::othertype', $shop{id}, $R::stockitems_id, $R::supplier_id, $R::org_qty, $R::org_price, $R::org_units, $new_qty, $new_price, '$R::discount', '$R::invoice', ?, '$sqldate{year}-$sqldate{month}-$sqldate{day}',$purtype, '$R::cnote', '$R::vat');") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA ADDED."; #is this latest price ? $results = $dbh->prepare("select count(id) notlast from purchases where date > '$sqldate{year}-$sqldate{month}-$sqldate{day}' and shop_id=$shop{id} and f_status<100 and stockitems_id=$R::stockitems_id") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if (! $ref->{notlast}) { #retreive links $results = $dbh->prepare("select distinct id from ritems where stockitems_id=$R::stockitems_id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); #update unit prices - ingredients; my %linkedritems; while ($ref = $results->fetchrow_hashref()) { $linkedritems{$ref->{id}} = 1; } foreach my $ritem_id (keys %linkedritems) { $results = $dbh->prepare("select id from ritem_prices where item_id='$ritem_id' order by id desc limit 1") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $id = $ref->{id}; if ($id) { # update existing price $results = $dbh->prepare("update ritem_prices set price='$new_price', supplier_id='$R::supplier_id', date='$sqldate{year}-$sqldate{month}-$sqldate{day}' where id='$id';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } else { #create new price record $results = $dbh->prepare("insert into ritem_prices (price,date,supplier_id,item_id) values ($new_price,'$sqldate{year}-$sqldate{month}-$sqldate{day}','$R::supplier_id','$ritem_id');") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } #$supplier_id } } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE purchases SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "STOCK DELETED."; } elsif (($R::command eq "Modify Data" and $R::org_price > 0 and $R::supplier_id>0)) { my %sqldate = dateforsql($R::date); $R::org_price *= 1; #/evaluate $R::org_qty *= 1; #/evaluate my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/notused #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty==1 and $R::vat==1) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount>0) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("UPDATE purchases SET othertype='$R::othertype', stockitems_id=$R::stockitems_id, supplier_id=$R::supplier_id, org_qty=$R::org_qty, org_price=$R::org_price, org_units=$R::org_units, qty=$new_qty, price=$new_price, discount='$R::discount', invoice='$R::invoice', description=?, date='$sqldate{year}-$sqldate{month}-$sqldate{day}', cnote='$R::cnote', vat='$R::vat', f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; #is this latest price ? $results = $dbh->prepare("select count(id) notlast from purchases where date > '$sqldate{year}-$sqldate{month}-$sqldate{day}' and shop_id=$shop{id} and f_status<100 and stockitems_id=$R::stockitems_id") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); if (! $ref->{notlast}) { #retreive links $results = $dbh->prepare("select distinct id from ritems where stockitems_id=$R::stockitems_id;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); #update unit prices - ingredients; my %linkedritems; while ($ref = $results->fetchrow_hashref()) { $linkedritems{$ref->{id}} = 1; } foreach my $ritem_id (keys %linkedritems) { $results = $dbh->prepare("select id from ritem_prices where item_id='$ritem_id' order by id desc limit 1") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $ref = $results->fetchrow_hashref(); my $id = $ref->{id}; if ($id) { # update existing price $results = $dbh->prepare("update ritem_prices set price='$new_price', supplier_id='$R::supplier_id', date='$sqldate{year}-$sqldate{month}-$sqldate{day}' where id='$id';") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } else { #create new price record $results = $dbh->prepare("insert into ritem_prices (price,date,supplier_id,item_id) values ($new_price,'$sqldate{year}-$sqldate{month}-$sqldate{day}','$R::supplier_id','$ritem_id');") or die $dbh->errstr(); $results->execute() or die $results->errstr(); } #$supplier_id } } } #/## /COMMANDS $defaultdate = datefromsql($R::pdate) || $tempdb{ssc_pur_date} || $date; #/ SPLITS #/quick mode if ($R::command eq "Turn Quick Mode ON") { $tempdb{pqm} = 2; } elsif ($R::command eq "Turn Quick Mode OFF") { $tempdb{pqm} = 1; } $tempdb{pqm} = 2 if (! $tempdb{pqm}); my $qm = $tempdb{pqm}; $temphtml .= "Limiting purchases for days of last invoice date.
" if (! $R::petty); $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[".($tempdb{stocktype} == $_ ? " $stocktypes{$database}{$_} " : "$stocktypes{$database}{$_}")."]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE PURCH $temphtml .= $table{start}; $temphtml .= "
Allocation: $subref->{name}"; } $temphtml .="
"; if ($stocktypes_account_type{$database}{$tempdb{stocktype}} == 2) { $temphtml .= "
Expence Category: "; my %otsel; $otsel{$ref->{othertype}} = "selected"; my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES"); my %parentnames; my %childrenparents; my %childrennames; my %standalones; $subresults = $dbh->prepare(" select id,name,type,vtype,parent,grp from bufc where grp <> 1 and shop_id=$shop{id} and year=$budgetyear and f_status < 100;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { if (! $subref->{parent} and $subref->{vtype} == 3) { $parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) { $standalones{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif ($subref->{type} == 1) { push @{$childrenparents{$subref->{parent}}},$subref->{id}; $childrennames { $subref->{id} } = $subref->{name}; } } $temphtml .="quickfind» find next»
"; } $temphtml .="
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
"; if ($stocktypes_account_type{$database}{$tempdb{stocktype}} == 2) { $temphtml .= "
Expence Category: "; my %otsel; #$otsel{$ref->{othertype}} = "selected"; my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES"); my %parentnames; my %childrenparents; my %childrennames; my %standalones; $subresults = $dbh->prepare("select id,name,type,vtype,parent,grp from bufc where grp <> 1 and shop_id=$shop{id} and year=$budgetyear and f_status < 100;") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); while ($subref = $subresults->fetchrow_hashref()) { if (! $subref->{parent} and $subref->{vtype} == 3) { $parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) { $standalones{$subref->{grp}}{$subref->{id}} = $subref->{name}; } elsif ($subref->{type} == 1) { push @{$childrenparents{$subref->{parent}}},$subref->{id}; $childrennames { $subref->{id} } = $subref->{name}; } } $temphtml .="quickfind» find next»
"; } $temphtml .="
"; if ($tempdb{pqm} == 1) { $temphtml .= " | "; } elsif ($tempdb{pqm} == 2) { $temphtml .= "Quick Mode ACTIVE | "; } $temphtml .= "View invoice number: "; $temphtml .= ""; $temphtml .= " or View invoice totals."; if ($R::petty) { $temphtml .= "
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
Function disabled! You must finish editing your Closing Stock in order to use this function"; $template =~ s/\@content\@/$temphtml/sgi; } #/ end PURCHASES editor elsif ($socketflag == 1 and $R::action eq "Other Purchases") { my $lastinvoicenumber = undef; my $lastsupplier=undef; my $totalforlastinvoice = undef; my $lastsupname; my $temphtml; my ($sttype,$purtype,$last_cspid,$purlimit,$purtotalslimit); my $lockflag=1; my $mydate; my $defaultdate; $onload="document.form1.description.focus();"; $defaultdate = datefromsql($R::pdate) || $date; $temphtml .= qq~ ~; $purtype = 2; #/default no petty cash $subresults = $dbh->prepare("SELECT max(last_pid) last_pid from cstock where f_status<100 and shop_id=$shop{id};") or die $dbh->errstr(); $subresults->execute() or die $subresults->errstr(); $subref = $subresults->fetchrow_hashref(); $subresults->finish(); $last_cspid = $subref->{last_pid}; $temphtml .= "
";
#/calendar JS
$temphtml .= qq~
~;
$template =~ s/\@title\@/Edit Other Purchases/sgi;
if (!$R::pdate) {
if (!$R::cpstart or !$R::cpend) {$R::cpstart=$date;$R::cpend=$date;}
my %sqldate = dateforsql($R::cpstart);
my %sqldate1 = dateforsql($R::cpend);
$purlimit = "and date>='$sqldate{year}-$sqldate{month}-$sqldate{day}' and date<='$sqldate1{year}-$sqldate1{month}-$sqldate1{day}'";
}
if ($R::petty) {
$subresults = $dbh->prepare("SELECT f_lock,DATE_FORMAT(date, '%e %M %Y') mydate from dcash where f_status<100 and shop_id=$shop{id} and date='$R::pdate';") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$subresults->finish();
$lockflag=$subref->{f_lock};
$mydate=$subref->{mydate};
$purlimit = "and pu.date='$R::pdate'"; #/ override after last closing stock
$purtype = 3;
$temphtml .= "PETTY CASH - $mydate
"; } else { #/ limit the purchases to smaller list $purtotalslimit = "limit 25"; #/ override after last closing stock } #/qty = org_qty * inv_units_alias_koef #/price = org_price / qty #/value=qty * price #/## PURCH EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { if ($R::othertype < 0) { $mess = "Error: Invalid Entry! Please re-enter the purchase!"; } else { my $supplier_id; my $wholesaler_id; $supplier_id = "NULL";$wholesaler_id = "NULL"; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my %sqldate = dateforsql($R::date); $R::org_price *=1; #/eval $R::org_qty *=1; #/eval my ($new_qty, $new_price); $new_qty = $R::org_qty;#/;*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty and $R::vat) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("INSERT INTO purchases (shop_id, stockitems_id, supplier_id,wholesaler_id, org_qty, org_price, org_units, qty, price, discount, invoice, description,date,type,cnote,othertype,vat) values (?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?,?);") or die $dbh->errstr(); $results->execute( $shop{id}, $supplier_id, $wholesaler_id, $R::org_qty, $R::org_price, $R::org_units, $new_qty, $new_price, $R::discount, $R::invoice, $R::description, "$sqldate{year}-$sqldate{month}-$sqldate{day}", $purtype, $R::cnote || '0', $R::othertype, $R::vat ) or die $results->errstr(); $mess = "DATA ADDED."; } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE purchases SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "STOCK DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { if ($R::othertype < 0) { $mess = "Error: Invalid Entry! Please re-enter the purchase!"; } else { my $supplier_id; my $wholesaler_id; $supplier_id = "NULL";$wholesaler_id = "NULL"; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my %sqldate = dateforsql($R::date); $R::org_price *=1; #/eval $R::org_qty *=1; #/eval my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty and $R::vat) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("UPDATE purchases SET stockitems_id=NULL, supplier_id=".$supplier_id.", wholesaler_id=".$wholesaler_id.", org_qty=$R::org_qty, org_price=$R::org_price, org_units=$R::org_units, qty=$new_qty, price=$new_price, discount='$R::discount', invoice='$R::invoice', description=?, date='$sqldate{year}-$sqldate{month}-$sqldate{day}', cnote='$R::cnote', vat='$R::vat', othertype=".$R::othertype.", f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE PURCH $temphtml .= $table{start}; $temphtml .= "";
$temphtml .= "
Description
Type
Supplier
CN
TAX
Invoice Qty
Invoice Units
Invoice Price
Discount (%)
Qty
Units
Price
Value
Invoice
date ";
$temphtml .= ":COMMANDS: " if ($lockflag ==1);
$temphtml .= " ";
$results = $dbh->prepare("
SELECT pu.id id, pu.qty qty, pu.org_price org_price, round(pu.price,2) price, round(price*qty,2) value,
pu.org_qty org_qty, pu.org_units org_units, pu.discount discount, pu.othertype othertype,
pu.invoice invoice, pu.description description, DAYOFMONTH(pu.date) day, MONTH(pu.date) month, YEAR(pu.date) year,
pu.date date,
pu.stockitems_id stockitems_id, pu.supplier_id supplier_id, pu.wholesaler_id wholesaler_id, pu.cnote cnote, pu.vat vat
FROM purchases pu
WHERE pu.shop_id=$shop{id} and pu.f_status<100
and pu.stockitems_id is null and pu.othertype is not null
and pu.type=$purtype
$purlimit
ORDER BY pu.date,pu.invoice
;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
#/debug print $results->rows;
while ($ref = $results->fetchrow_hashref()) {
if ($R::command ne "modify" or ($R::command eq "modify" and $R::itemid != $ref->{id})) {
if (defined $lastinvoicenumber and ($lastinvoicenumber ne $ref->{invoice})) {
$temphtml .= "
$lastsupname
Total:
$currency".pr($totalforlastinvoice)."
$lastinvoicenumber
";
$totalforlastinvoice = 0;
}
$lastinvoicenumber = $ref->{invoice};
$lastsupplier = $ref->{supplier_id};
$totalforlastinvoice += $ref->{price}*$ref->{qty};
$temphtml .= "";
$temphtml .= "
{id}&pdate=$R::pdate&dcashid=$R::dcashid\">$ref->{description} ";
if ($ref->{othertype} != -1) {
$subresults = $dbh->prepare("
SELECT name FROM bufc WHERE f_status<100 and year=$budgetyear and id=$ref->{othertype} and shop_id=$shop{id};") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$temphtml .="$subref->{name} "
} else {$temphtml .="other "}
my ($suptable,$supid,$suptype, $useshop, $supname);
if (! $ref->{supplier_id}) {$suptable = 'wholesalers'; $supid=$ref->{wholesaler_id};$suptype=1;
} else {$suptable = 'suppliers';$useshop="and shop_id=$shop{id}";$supid=$ref->{supplier_id};$suptype=2;}
#/fetch supplier/wholesaler
$subresults = $dbh->prepare("
SELECT name FROM $suptable WHERE id=$supid $useshop and f_status<100;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
$subref = $subresults->fetchrow_hashref();
$supname = $subref->{name};
$lastsupname = $supname;
$temphtml .= "
$supname
$cnote{$ref->{cnote}}
$cnote{$ref->{vat}}
$ref->{org_qty}
${ $unitalias{$ref->{org_units}} }[0]
$ref->{org_price}
$ref->{discount}%
$ref->{qty}
$units{$ref->{units}}
$ref->{price}
$ref->{value}
$ref->{invoice}
$ref->{day}-$ref->{month}-$ref->{year}
";
$temphtml .= "
{id}&pdate=$R::pdate&dcashid=$R::dcashid&cpstart=$R::cpstart&cpend=$R::cpend\" onclick=\"return confirm('DELETE ITEM: $ref->{item} ?');\">$icons{d}
{id}&pdate=$R::pdate&dcashid=$R::dcashid&cpstart=$R::cpstart&cpend=$R::cpend\">$icons{m}
" if ($lockflag ==1);
$temphtml .= " ";
} else {
$temphtml .= "";
$temphtml .= " $icons{det} {description}\" $calt> ";
my %otsel;
$otsel{$ref->{othertype}} = "selected";
my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES");
my %parentnames;
my %childrenparents;
my %childrennames;
my %standalones;
$subresults = $dbh->prepare("select id,name,type,vtype,parent,grp from bufc
where
grp <> 1
and shop_id=$shop{id}
and year=$budgetyear
and f_status < 100;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
while ($subref = $subresults->fetchrow_hashref()) {
if (! $subref->{parent} and $subref->{vtype} == 3) {
$parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name};
} elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) {
$standalones{$subref->{grp}}{$subref->{id}} = $subref->{name};
} elsif ($subref->{type} == 1) {
push @{$childrenparents{$subref->{parent}}},$subref->{id};
$childrennames { $subref->{id} } = $subref->{name};
}
}
$temphtml .="quickfind»
find next»
";
my (%ssel,%usel,$ct,$vt);
$usel{$ref->{org_units}} = "SELECTED";
if (! $ref->{supplier_id})
{ $ssel{"w".$ref->{wholesaler_id}}="SELECTED" }
else { $ssel{"s".$ref->{supplier_id}}="SELECTED" }
$temphtml .="
{org_qty}\" $calt_e3>
";
$temphtml .=" {org_price}\" $calt_e>
{discount}\" $calt_e>
* * * *
{invoice}\" $calt>
{day}-$ref->{month}-$ref->{year}\" $calt>
$icons{cal}
";
}
}
$temphtml .= "
$lastsupname
Total:
$currency".pr($totalforlastinvoice)."
$lastinvoicenumber
";
if ($R::petty) {
#/## TOTALS PETTYCASH
my %calc;
my $results = $dbh->prepare("SELECT SUM(org_price) FROM purchases WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate' and type=3;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} = $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM loans WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
$results = $dbh->prepare("SELECT SUM(amount) FROM creditors WHERE shop_id=$shop{id} and f_status<100 and date='$R::pdate';") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{amount} += $ref;
#/comparation/substraction
$results = $dbh->prepare("SELECT pettycash FROM dcash WHERE shop_id=$shop{id} and f_status<100 and id=$R::dcashid;") or die $dbh->errstr();
$results->execute() or die $results->errstr();
$ref = ($results->fetchrow_array())[0];
$calc{diff} = $calc{amount} - $ref;
$calc{aspercash} = $ref;
if (sprintf("%.2f",$calc{diff}) == 0) {$linecolor=""} else {$linecolor=$errorcolor}
$temphtml .= "
Total as per Petty Cash ".pr($calc{amount})."
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary ";
$temphtml .= "
";
my %grps = (2=>"FIXED COSTS / EXPENSES",3=>"SALARIES & WAGES");
my %parentnames;
my %childrenparents;
my %childrennames;
my %standalones;
$subresults = $dbh->prepare("select id,name,type,vtype,parent,grp from bufc
where
grp <> 1
and shop_id=$shop{id}
and year=$budgetyear
and f_status < 100;") or die $dbh->errstr();
$subresults->execute() or die $subresults->errstr();
while ($subref = $subresults->fetchrow_hashref()) {
if (! $subref->{parent} and $subref->{vtype} == 3) {
$parentnames{$subref->{grp}}{$subref->{id}} = $subref->{name};
} elsif (! $subref->{parent} and $subref->{vtype} != 3 and $subref->{type} == 1) {
$standalones{$subref->{grp}}{$subref->{id}} = $subref->{name};
} elsif ($subref->{type} == 1) {
push @{$childrenparents{$subref->{parent}}},$subref->{id};
$childrennames { $subref->{id} } = $subref->{name};
}
}
$temphtml .="
quickfind»
find next»
";
$temphtml .="
";
$temphtml .="
* * * *
$icons{cal}
";
}
$temphtml .= qq ~
Please specify period: Start date: $icons{cal} --> End date: $icons{cal}
"; } if ($R::petty) { $temphtml .= "
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
";
$template =~ s/\@content\@/$temphtml/sgi;
} #/ end OTHER PURCHASES editor
elsif ($socketflag == 1 and $R::action eq "Generic Calenedar") {
my $year = (localtime(time))[5] + 1900;
$year = $R::year if ($R::year);
$template =~ s/\@title\@/Generic Calendar - $year/sgi;
my $temphtml;
$temphtml .= "";
$temphtml .= ""; } else { #/ limit the purchases to smaller list $purtotalslimit = "limit 25"; #/ override after last closing stock } #/qty = org_qty * inv_units_alias_koef #/price = org_price / qty #/value=qty * price #/## PURCH EDIT COMMANDS if ($R::command eq "Add Data" or ($R::command eq "" and $R::hcommand eq "Add Data")) { if ($R::othertype < 0) { $mess = "Error: Invalid Entry! Please re-enter the purchase!"; } else { my $supplier_id; my $wholesaler_id; $supplier_id = "NULL";$wholesaler_id = "NULL"; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my %sqldate = dateforsql($R::date); $R::org_price *=1; #/eval $R::org_qty *=1; #/eval my ($new_qty, $new_price); $new_qty = $R::org_qty;#/;*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty and $R::vat) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("INSERT INTO purchases (shop_id, stockitems_id, supplier_id,wholesaler_id, org_qty, org_price, org_units, qty, price, discount, invoice, description,date,type,cnote,othertype,vat) values (?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?,?);") or die $dbh->errstr(); $results->execute( $shop{id}, $supplier_id, $wholesaler_id, $R::org_qty, $R::org_price, $R::org_units, $new_qty, $new_price, $R::discount, $R::invoice, $R::description, "$sqldate{year}-$sqldate{month}-$sqldate{day}", $purtype, $R::cnote || '0', $R::othertype, $R::vat ) or die $results->errstr(); $mess = "DATA ADDED."; } } elsif ($R::command eq "delete") { my $results = $dbh->prepare("UPDATE purchases SET f_status=101 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute() or die $results->errstr(); $mess = "STOCK DELETED."; } elsif ($R::command eq "Modify Data" or ($R::command eq "" and $R::hcommand eq "Modify Data")) { if ($R::othertype < 0) { $mess = "Error: Invalid Entry! Please re-enter the purchase!"; } else { my $supplier_id; my $wholesaler_id; $supplier_id = "NULL";$wholesaler_id = "NULL"; if ($R::supplier_id =~ /(w)(\d+)/) {$wholesaler_id=$2} elsif ($R::supplier_id =~ /(s)(\d+)/) {$supplier_id=$2} my %sqldate = dateforsql($R::date); $R::org_price *=1; #/eval $R::org_qty *=1; #/eval my ($new_qty, $new_price); $new_qty = $R::org_qty;#/*${unitalias{$R::org_units}}[1]; $new_price = $R::org_price/$R::org_qty; #/remove VAT for single price if pettycash purchase of item having vat if ($R::petty and $R::vat) {$new_price = $new_price /((100 + $cvat)/100)} if ($R::discount) {$new_price = $new_price - $new_price*$R::discount/100} my $results = $dbh->prepare("UPDATE purchases SET stockitems_id=NULL, supplier_id=".$supplier_id.", wholesaler_id=".$wholesaler_id.", org_qty=$R::org_qty, org_price=$R::org_price, org_units=$R::org_units, qty=$new_qty, price=$new_price, discount='$R::discount', invoice='$R::invoice', description=?, date='$sqldate{year}-$sqldate{month}-$sqldate{day}', cnote='$R::cnote', vat='$R::vat', othertype=".$R::othertype.", f_status=1 WHERE id=$R::itemid;") or die $dbh->errstr(); $results->execute($R::description) or die $results->errstr(); $mess = "DATA MODIFIED."; } } #/## /COMMANDS #/ SPLITS $temphtml .= "STOCK TYPE:
"; foreach my $acctype (sort keys %{ $stocktypes_breakdown{$database} }) { $temphtml .= "($account_types{$acctype}: "; foreach (sort {$stocktypes{$database}{$a} cmp $stocktypes{$database}{$b}} keys %{ $stocktypes_breakdown{$database}{$acctype} } ) { $temphtml .= "[$stocktypes{$database}{$_}]"; } $temphtml .= ")
"; } $temphtml .= " | Other Expences"; $temphtml .= " | Wages/Loans" if ($R::petty); $temphtml .= " | Creditors" if ($R::petty); $temphtml .= "
"; #/ DRAW TABLE PURCH $temphtml .= $table{start}; $temphtml .= "
Total as per cash-up ".pr($calc{aspercash})."
Shortfall for account of the management ".pr($calc{diff})."
Petty Cash Summary
Please specify period: Start date: $icons{cal} --> End date: $icons{cal}
"; } if ($R::petty) { $temphtml .= "
Back to Cash-up
"; } $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= ""; $temphtml .= "
Selected year: ";
$temphtml .= " ";
$temphtml .= "
"; #/ Month Cell
$temphtml .= "
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
![](miscimg/c_".$dayflags{$offdays{$month}{$day}}.".gif)
" if ($offdays{$month}{$day});
} elsif ($R::mode eq "edit") {
#/$temphtml .= "
- $daytypes{$_} 
$ip/$que - ";
if (scalar @badjobs) {
$printershtml .= "ERROR