newphpbees
-
Posts
65 -
Joined
-
Last visited
Never
Posts posted by newphpbees
-
-
Thanks for your code, now I'm thinking on my where clause to get only the non-zero and null.
Thank you
-
Good day!
Hi,
I encountered problem in Count.
I have this data:
REFNUM----LOT_CODE----PCODE--wip_chemicalweighing--wip_compounding-------wip_extrusion--wip_forming--wip_deflashing
000001----000001P35M--P35M---0.00------------------0.00------------------0.00-----------0.00---------40.00---------
000002----000002P35M--P35M---73.30-----------------NULL------------------NULL-----------NULL---------NULL---------
and I have this code:
SELECT PCODE, COUNT(LOT_CODE) AS lot_chemicalweighing, COUNT(wip_chemicalweighing + wip_compounding + wip_extrusion + wip_forming + wip_deflashing) AS virtual FROM kanban_data WHERE wip_chemicalweighing != '0.00' OR 'NULL' AND wip_compounding != '0.00' OR 'NULL' AND wip_extrusion != '0.00' OR 'NULL'AND wip_forming != '0.00' OR 'NULL' AND wip_deflashing != '0.00' OR 'NULL' GROUP BY PCODE;
And the output of this code is :
PCODE-- lot_chemicalweighing---virtual----
P35M----1----------------------0--------
the lot_chemicalweighing is correct but the virtual is wrong, because it should be 2 because I have 1 data in wip_chemicalweighing and 1 in wip_deflashing.
Thank you
-
I already resolved it using this code:
$sql = "UPDATE kanban_data kd SET wip_chemicalweighing = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), wip_compounding = (SELECT ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), wip_forming = (SELECT ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), wip_deflashing = (SELECT ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM)";
Thank you
-
Hi
Have this update query:
UPDATE kanban_data kd SET (wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashing) = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing, ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding, ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion', 'ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM);
and I got this error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashi' at line 1
(16 ms taken)
Thank you
-
I try your suggested code and still it was one line but i really appreciate your help..
Actually i already revised my code to change the design but still it was one side or one line.
here is my code:
<div> {section name=att loop=$rs_label} <table class="stat" border = "0" width="auto"> <tr> <td colspan="6" align="center"><b>{$rs_label[att].COMPANY_NAME}</b></td> <tr/> <tr> <td colspan="3" align="center"><b>{$rs_label[att].fullName}</b></td> <td colspan="3" align="center"><b>{$rs_label[att].Rate}</b></td> </tr> <tr> <td colspan="3"><b>{$rs_label[att].PERIOD} {$rs_label[att].FROM_DATE}-{$rs_label[att].TO_DATE}</b></td> <td colspan="3" align="center"><b>{$rs_label[att].RATE_Label}</b></td> <tr/> <tr> <td colspan="3"><b>{$rs_label[att].EARNINGS}</b></td> <td colspan="3"><b>{$rs_label[att].DEDUCTIONS_Label}</b></td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right">{$rs_label[att].HRS}</td><td colspan="1" align="right">{$rs_label[att].AMOUNT}</td><td colspan="1"></td><td colspan="1" align="right">{$rs_label[att].HRS}</td><td colspan="1" align="right">{$rs_label[att].AMOUNT}</td> </tr> <tr> <td colspan="1">{$rs_label[att].BASIC_PAY_Label}</td><td colspan="1" align="right">{$rs_label[att].Reg_Hours}</td><td colspan="1" align="right">{$rs_label[att].Basic_Pay}</td><td colspan="1">{$rs_label[att].Late_Label}</td><td colspan="1" align="right">{$rs_label[att].Late_Hours}</td><td colspan="1" align="right">{$rs_label[att].Late}</td> </tr> <tr> <td colspan="1">{$rs_label[att].REGULAR_OVERTIME}</td><td colspan="1" align="right">{$rs_label[att].OTReg_Hours}</td><td colspan="1" align="right">{$rs_label[att].OTReg_Amt}</td><td colspan="1">{$rs_label[att].WITHHOLDING_TAX_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].TAX}</td> </tr> <tr> <td colspan="1">{$rs_label[att].SUN_SPEC_HOLIDAY}</td><td colspan="1" align="right">{$rs_label[att].SunReg_Hours}</td><td colspan="1" align="right">{$rs_label[att].SunReg_Amt}</td><td colspan="1">{$rs_label[att].SSS_CONTRIBUTION_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].SSS}</td> </tr> <tr> <td colspan="1">{$rs_label[att].SUN_SPEC_HOL_OT}</td><td colspan="1" align="right">{$rs_label[att].OTSun_Hours}</td><td colspan="1" align="right">{$rs_label[att].OTSun_Amt}</td><td colspan="1">{$rs_label[att].MEDICARE_CONT_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].PCHL}</td> </tr> <tr> <td colspan="1">{$rs_label[att].REG_HOLIDAY}</td><td colspan="1" align="right">{$rs_label[att].HolReg_Hours}</td><td colspan="1" align="right">{$rs_label[att].HolReg_Amt}</td><td colspan="1">{$rs_label[att].HDMF_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].HDMF}</td> </tr> <tr> <td colspan="1">{$rs_label[att].REG_HOLIDAY_OT}</td><td colspan="1" align="right">{$rs_label[att].HolRegOT_Hours}</td><td colspan="1" align="right">{$rs_label[att].HolRegOT_Amt}</td><td colspan="1">{$rs_label[att].SSS_Loan_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].SSS_Amor}</td> </tr> <tr> <td colspan="1">{$rs_label[att].HOLIDAY_LEAVE}</td><td colspan="1" align="right">{$rs_label[att].HolLeave_Hours_Tot}</td><td colspan="1" align="right">{$rs_label[att].HolLeave_Amt}</td><td colspan="1">{$rs_label[att].HDMF_Loan_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].HDMF_Amor}</td> </tr> <tr> <td colspan="1">{$rs_label[att].NIGHT_PREMIUM_Label}</td><td colspan="1" align="right">{$rs_label[att].NIGHT_PREMIUM}</td><td colspan="1" align="right">{$rs_label[att].NP_Amt}</td><td colspan="1">{$rs_label[att].TAX_ADJT_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].TaxAjt}</td> </tr> <tr> <td colspan="1">{$rs_label[att].MEAL_ALLOWANCE_Label}</td><td colspan="1" align="right">{$rs_label[att].MEAL_ALLOWANCE}</td><td colspan="1" align="right">{$rs_label[att].Meal_Amt}</td><td colspan="1">{$rs_label[att].Cash_Advance_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].CashAdvance}</td> </tr> <tr> <td colspan="1">{$rs_label[att].COLA}</td><td colspan="1" align="right">{$rs_label[att].Cola_Hours}</td><td colspan="1" align="right">{$rs_label[att].Cola_Amt}</td><td colspan="1">{$rs_label[att].Advances_Shirt_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].AdvanceShirt}</td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1">{$rs_label[att].Advances_Medical_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].AdvanceMed}</td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1">{$rs_label[att].Advances_Others_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].AdvanceOther}</td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1">{$rs_label[att].CANTEEN_Label}</td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].Canteen}</td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].LINE_Label}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].LINE_Label}</td> </tr> <tr> <td colspan="1"><b>{$rs_label[att].TOTAL_EARNINGS_Label}</b></td><td colspan="1" align="right"></td><td colspan="1" align="right"><b>{$rs_label[att].TotalEarnings}</b></td><td colspan="1"><b>{$rs_label[att].TOTAL_DEDUCTIONS_Label}</b></td><td colspan="1" align="right"></td><td colspan="1" align="right"><b>{$rs_label[att].TotalDeductions}</b></td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].Receipt_Pay}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].Receipt_Detail}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].Receipt_Detail1}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].Receipt_Detail2}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].fullName}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].FIRST_LINE}</td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td> </tr> <tr> <td colspan="3" align="center">{$rs_label[att].SIGNATURE}</td><td colspan="1"><b>{$rs_label[att].TAKE_HOME_PAY_Label}</b></td><td colspan="1" align="right"></td><td colspan="1" align="right"><b>{$rs_label[att].TakeHomePay}</b></td> </tr> <tr> <td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right"></td><td colspan="1"></td><td colspan="1" align="right"></td><td colspan="1" align="right">{$rs_label[att].SECOND_LINE}</td> </tr> <tr> <td colspan="6">{$rs_label[att].FOURTH_LINE}</td> </tr> </table> {/section} </div> [code] I want is like this Payslip Employee 1 Payslip Employee 2 Payslip Employee 3 List of data1 List of data 2 List of data 3 a And so on… Now the result of my code is : Payslip Employee 1 List of data1 Payslip Employee 2 List of data2 Payslip Employee 3 List of data3 Payslip Employee 4 List of data4 Payslip Employee 5 List of data5 Payslip Employee 6 List of data6 Thank you -
Hi...
Good day!
I just want to know how can I change the format of my table. here is my code:
<?php include 'config.php'; $sql = "SELECT DATE_FORMAT(d.FROM_DATE, '%M %c') AS FROM_DATE, DATE_FORMAT(d.TO_DATE, '%M %c, %Y') AS TO_DATE, g.EMP_NO, g.fullName, g.Rate, g.Reg_Hours, g.Basic_Pay, g.OTReg_Hours, g.SunReg_Hours, g.OTSun_Hours, g.HolReg_Hours, g.HolRegOT_Hours, g.HolLeave_Hours_Tot, SUM(g.NPReg_Hours + g.NPSun_Hours + g.NPHol_Hours) AS NIGHT_PREMIUM, SUM(g.MealReg_Hours + g.MealSun_Hours) AS MEAL_ALLOWANCE, g.Cola_Hours, g.Basic_Pay, g.OTReg_Amt, g.SunReg_Amt, g.OTSun_Amt, g.HolReg_Amt, g.HolRegOT_Amt, g.HolLeave_Amt, g.NP_Amt, g.Meal_Amt, g.Cola_Amt, p.COMPANY_NAME, p.PERIOD, p.NAME, p.FIRST_LINE, p.RATE_Label, p.EARNINGS, p.HRS, p.AMOUNT, p.BASIC_PAY_Label, p.REGULAR_OVERTIME, p.SUN_SPEC_HOLIDAY, p.SUN_SPEC_HOL_OT, p.REG_HOLIDAY, p.REG_HOLIDAY_OT, p.HOLIDAY_LEAVE, p.NIGHT_PREMIUM_Label, p.MEAL_ALLOWANCE_Label, p.COLA, p.13TH_MONTH, p.SECOND_LINE FROM $PAYROLL.casual_payslip_label p, $PAYROLL.casual_generate_payroll g, $PAYROLL.casual_date_upload d GROUP BY g.EMP_NO"or die(mysql_error()); $rs_label = $conn2->GetAll($sql); $smarty->assign('rs_label', $rs_label); $smarty->display('payslip.tpl'); $smarty->display('casual_payslip.tpl'); ?> <div> {section name=att loop=$rs_label} <table class="stat"> <tr> <td>{$rs_label[att].COMPANY_NAME}</td> <tr/> <tr> <td>{$rs_label[att].PERIOD} {$rs_label[att].FROM_DATE}-{$rs_label[att].TO_DATE}</td> <tr/> <tr> <td>{$rs_label[att].NAME} {$rs_label[att].fullName}</td> </tr> <tr> <td>{$rs_label[att].FIRST_LINE}</td> </tr> <tr> <td>{$rs_label[att].RATE_Label} {$rs_label[att].Rate}</td> </tr> <tr> <td>{$rs_label[att].EARNINGS} {$rs_label[att].HRS} {$rs_label[att].AMOUNT}</td> </tr> <tr> <td>{$rs_label[att].BASIC_PAY_Label} {$rs_label[att].Reg_Hours} {$rs_label[att].Basic_Pay}</td> </tr> <tr> <td>{$rs_label[att].REGULAR_OVERTIME} {$rs_label[att].OTReg_Hours} {$rs_label[att].OTReg_Amt}</td> </tr> <tr> <td>{$rs_label[att].SUN_SPEC_HOLIDAY} {$rs_label[att].SunReg_Hours} {$rs_label[att].SunReg_Amt}</td> </tr> <tr> <td>{$rs_label[att].SUN_SPEC_HOL_OT} {$rs_label[att].OTSun_Hours} {$rs_label[att].OTSun_Amt}</td> </tr> <tr> <td>{$rs_label[att].REG_HOLIDAY} {$rs_label[att].HolReg_Hours} {$rs_label[att].HolReg_Amt}</td> </tr> <tr> <td>{$rs_label[att].REG_HOLIDAY_OT} {$rs_label[att].HolRegOT_Hours} {$rs_label[att].HolRegOT_Amt}</td> </tr> <tr> <td>{$rs_label[att].HOLIDAY_LEAVE} {$rs_label[att].HolLeave_Hours_Tot} {$rs_label[att].HolLeave_Amt}</td> </tr> <tr> <td>{$rs_label[att].NIGHT_PREMIUM_Label} {$rs_label[att].NIGHT_PREMIUM} {$rs_label[att].NP_Amt}</td> </tr> <tr> <td>{$rs_label[att].MEAL_ALLOWANCE_Label} {$rs_label[att].MEAL_ALLOWANCE} {$rs_label[att].Meal_Amt}</td> </tr> <tr> <td>{$rs_label[att].COLA}{$rs_label[att].Cola_Hours} {$rs_label[att].Cola_Amt}</td> </tr> <tr> <td>{$rs_label[att].SECOND_LINE}</td> </tr> </table> {/section} </div>the output of this code:
Company Name
Period : jul 09- July 22,2011
Name : Juan Dela Cruz
Rate : 339.80
EARNINGS HRS AMOUNT
Basic Pay 91.45 3,884.34
Regular Overtime 0.00 0.00
Sun/Spec Holiday 0.00 0.00
Sun/Spec Hol. OT 0.00 0.00
Reg. Holiday 0.00 0.00
Reg. Holiday OT 0.00 0.00
Holiday/Leave 0.00 0.00
Night Premium 0.00 0.00
Meal Allowance 0.00 0.00
COLA (P28 Allow) 0.00 0.00
13th Month 0.00
Company Name
Period : jul 09- July 22,2011
Name : Ann Chin
Rate : 339.80
EARNINGS HRS AMOUNT
Basic Pay 100.00 4247.5
Regular Overtime 0.00 0.00
Sun/Spec Holiday 0.00 0.00
Sun/Spec Hol. OT 0.00 0.00
Reg. Holiday 0.00 0.00
Reg. Holiday OT 0.00 0.00
Holiday/Leave 0.00 0.00
Night Premium 0.00 0.00
Meal Allowance 0.00 0.00
COLA (P28 Allow) 0.00 0.00
13th Month 0.00
Company Name
Period : jul 09- July 22,2011
Name : John Lee
Rate : 339.80
EARNINGS HRS AMOUNT
Basic Pay 101 4289.96
Regular Overtime 0.00 0.00
Sun/Spec Holiday 0.00 0.00
Sun/Spec Hol. OT 0.00 0.00
Reg. Holiday 0.00 0.00
Reg. Holiday OT 0.00 0.00
Holiday/Leave 0.00 0.00
Night Premium 0.00 0.00
Meal Allowance 0.00 0.00
COLA (P28 Allow) 0.00 0.00
13th Month 0.00
Company Name
Period : jul 09- July 22,2011
Name : Mary Ann Chan
Rate : 339.80
EARNINGS HRS AMOUNT
Basic Pay 90 3,882.75
Regular Overtime 0.00 0.00
Sun/Spec Holiday 0.00 0.00
Sun/Spec Hol. OT 0.00 0.00
Reg. Holiday 0.00 0.00
Reg. Holiday OT 0.00 0.00
Holiday/Leave 0.00 0.00
Night Premium 0.00 0.00
Meal Allowance 0.00 0.00
COLA (P28 Allow) 0.00 0.00
13th Month 0.00
I have a lot of employee so the data is long.
I attach the format I want to happen.
Thank you
-
How can i displayed it in smarty template?
Thank you
-
Hi...
Good day!
I have table that has a field from_date and to_date.
Now I just want to know if how can I display as table format the dates between from_date to_date.
Like this
from_date: 2011-12-16
to_date: 2011-12-31
I want to display it:
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 // table format.
Thank you
-
I have this new code:
$from_time = strtotime($IN); $to_time = strtotime($OUT); $OTHours = round(abs($to_time - $from_time) / 3600);
$IN = 2011-12-22 13:30;
$OUT = 2011-12-22 15:30;
from this code the $OTHours result is 1.00
it should be 1.30
by the way the OTHours field is Decimal Type.
-
Is it possible that first i only get the total hours and I save it in one field? then i add one field to put the total minutes? from sum of Hours?
Thank you
-
In payroll minutes was divided into 60.
i tried this:
$TotHours = substr($Hours, 0, 3); $Minutes = substr($Hours,4,2); $Minutes = substr($Hours,3,2); $Minutes_Convert = ($Minutes / 60); $Minutes_Convert = number_format($Minutes_Convert, 2, '.', ''); $Hours = $TotHours + $Minutes_Convert;
but i encountered problem when TotHours = 100 the substr was not capable.
-
Hi..
I got an issue in getting the total time.
I have this code to get the sum of total time:
$result = mysql_query("INSERT INTO payroll.casual_hours(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM payroll.casual_att WHERE DATE_FORMAT(LOGOUT, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') GROUP BY EMP_NO") or die(mysql_error());Rendered:
09:00:00
09:00:00
09:00:00
08:00:00
08:00:00
07:48:00
and the result of this is 50:48
but I need result is 50:80
the minutes is divided to 60
Is there any way to get the 50:80 result.
Thank you so much..
-
No...
It was inputted in a textbox...
Thank you
-
Do you have any example code?
Thank you
-
if there is a date...how???
-
I have two textboxes that inputted time, and I want to get the difference between two time.
for example:
$IN = 13:35;
$OUT = 17:35;
$OTHours = ($OUT - $IN);
$OTHours = 4.00;
and it is correct, but I have a scenario like this:
$IN = 21:35;
$OUT = 05:35;
$OTHours = -16.00;
it should be 8.00.
Any help is highly appreciated.
Thank you..
-
Can you give the whole code sample for that usingphp?
Thank you
-
I already resolved it now using this code:
<?php include 'config.php'; backup_tables('localhost','root','','payroll'); /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file // $myfoldername = "backup_DBPayroll";//your folders name // $handle = fopen(getcwd().\.$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); //$myfoldername = $_SERVER['DOCUMENT_ROOT']."/backup_DBPayroll/";//your folders name //$handle = fopen($myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); $myfoldername = "../backup_DBPayroll/";//your folders name $handle = fopen($myfoldername.'operatives-db-backup-'.date('m-d-Y').'-'.(md5(implode(',',$tables))).'.sql','w+'); // $myfoldername = $_SERVER['DOCUMENT_ROOT']."/path-to/backup_DBPayroll/";//your folders name // $handle = fopen($myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); // $handle = fopen('db-backup-'.date('m-d-Y').'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } $smarty->display('payroll_report_cat.tpl'); //$smarty->display('header_cat.tpl'); $smarty->display('backup.tpl'); $smarty->display('footer.tpl'); ?>Now, I just want to ask if with this same code file is possible to add code for back up one table as csv file?
If possible how?
Thank you
-
Hi..
I've seen code for back up database but when I run the code the database was backup outside the folder. I want to put the back up database inside the folder
here is the code:
<?php include 'config.php'; backup_tables('localhost','root','','payroll'); /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $myfoldername = "backup_DBPayroll";//your folders name $handle = fopen(getcwd().$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); // $handle = fopen('db-backup-'.date('m-d-Y').'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } $smarty->display('header_cat.tpl'); $smarty->display('backup.tpl'); $smarty->display('footer.tpl'); ?>Thank you in advance
-
Hi...
I have three types of night premium and it only computed of the schedule is night shift
I have this Night Premium Types:
- NightPremiumRegular (Monday-Saturday)
-NightPremiumSunday (Sunday)
- NightPremiumHoliday
I created table which has a listed of holidays date.
Now I encountered problem in sum of hours in NightPremiumHoliday
here is my code:
//===================Display Night Premium========================= $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS SUNDAY, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') = 'Sunday'"; $rsNPSun = $conn2->Execute($sql); $NPSunHours = $rsNPSun->fields['Rendered']; $NPSunDate = $rsNPSun->fields['SUNDAY']; $NPSunHours = substr($NPSunHours, 0, 5); $NPSunHours = str_replace(':', '.', $NPSunHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumSunday'"; $rsOTCatSun = $conn2->Execute($sql); $NPSun_OP = $rsOTCatSun->fields['OP']; $NPSunAmt = (($Rate / 8 * 1.35 * $NPSun_OP) * $NPSunHours); $NPSunAmt = number_format($NPSunAmt, 2, '.', ''); $smarty->assign('NPSunHours', $NPSunHours); $smarty->assign('NPSunAmt', $NPSunAmt); //=================NP REG======= $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS Week_Days, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')"; $rsNPReg = $conn2->Execute($sql); $NPRegHours = $rsNPReg->fields['Rendered']; $NPRegDate = $rsNPReg->fields['Week_Days']; $NPRegHours = substr($NPRegHours, 0, 5); $NPRegHours = str_replace(':', '.', $NPRegHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumRegular'"; $rsOTCatSun = $conn2->Execute($sql); $NPReg_OP = $rsOTCatSun->fields['OP']; $NPRegAmt = (($Rate / 8 * $NPReg_OP) * $NPRegHours); $NPRegAmt = number_format($NPRegAmt, 2, '.', ''); $smarty->assign('NPRegHours', $NPRegHours); $smarty->assign('NPRegAmt', $NPRegAmt); //=======================Night Premium Holiday============== $sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $PAYROLL.holiday_date h WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND DATE(LOGIN) = h.holiday_date AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID'"; $rsNPHol = $conn2->Execute($sql); $NPHolHours = $rsNPHol->fields['Rendered']; $NPHolHours = substr($NPHolHours, 0, 5); $NPHolHours = str_replace(':', '.', $NPHolHours); $sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumHoliday'"; $rsOTCatSun = $conn2->Execute($sql); $NPHol_OP = $rsOTCatSun->fields['OP']; $NPHolAmt = (($Rate / 8 * 2.05 * $NPHol_OP) * $NPHolHours); $NPHolAmt = number_format($NPHolAmt, 2, '.', ''); $smarty->assign('NPHolHours', $NPHolHours); $smarty->assign('NPHolAmt', $NPHolAmt); $NPHours = ($NPRegHours + $NPSunHours + $NPHolHours); $NPHours = number_format($NPHours, 2, '.', ''); $NP_Amt = ($NPRegAmt + $NPSunAmt + $NPHolAmt); $NP_Amt = number_format($NP_Amt, 2, '.', ''); $smarty->assign('NP_Hours', $NPHours); $smarty->assign('NP_Amt', $NP_Amt);for example:
I have attendance 2012-01-01 and it's sunday and it is also holiday..
And the rendered is 8
so the NightPremiumSunday and NightPremiumHolidays computed, but i got problem in computations of NPHours.
I got NPHours = 16, supposively 8, but because it is sunday and holiday it sum the result of hours in Sunday and Holiday..
How can I only get 8 hours if my date is holiday?
Thank you so much..
Any help is highly appreciated..
feel free to ask me if theirs a question.
-
HOw can I add in my select statement to condition that if the emp_no, log_in, log_out is equal to NRS table the data will have a color...
Like i do if the login or logout is NULL.
Thank you
-
Hi...
I have query in highlighting null data using this code:
<?php include 'config.php'; $currentEmpID = $_SESSION['empID']; if(!isset($_POST['Regsubmit_'])){ $DATE1 = $_GET['Regfirstinput']; $DATE2 = $_GET['Regsecondinput']; $sql = "SELECT DISTINCT IF(ISNULL(a.LOG_IN), 'rdc', '') AS LOGIN_CLASS, IF(ISNULL(a.LOG_OUT), 'rdc', '') AS LOGOUT_CLASS, a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT FROM $ATTENDANCE.attendance_build AS a JOIN $ADODB_DB.employment em ON (a.EMP_ID = em.EMP_NO AND em.STATUS IN ('Reg Operatives', 'Reg Staff')) WHERE LOG_IN BETWEEN '$DATE1' AND '$DATE2' OR ISNULL(LOG_IN) OR ISNULL(LOG_OUT)"; $DTR = $conn3->GetAll($sql); $smarty->assign('attendance', $DTR); } $smarty->display('header_att.tpl'); $smarty->display('RegAttendance.tpl'); $smarty->display('footer.tpl'); ?>and here is the tpl code:
{section name=att loop=$attendance} <tr> <td colspan="2">{$attendance[att].EMP_ID}</td> <td colspan="2">{$attendance[att].FULLNAME}</td> <td colspan="2" class="{$attendance[att].LOGIN_CLASS}">{$attendance[att].LOG_IN|date_format:"%d-%m-%Y %I:%M %p"}</td> <td colspan="2" class="{$attendance[att].LOGOUT_CLASS}">{$attendance[att].LOG_OUT|date_format:"%d-%m-%Y %I:%M %p"}</td> </tr> {sectionelse} <tr><td colspan="1">No DATA</td></tr> {/section}this code highlight the null value of login or logout or both.
this is the css:
.rdc {background-color:#ff0000;}Now, I need to revised my query statement, because i have separate code for adding attendance if the employee has no attendance or no login or no logout.
I just want to happen is if the employee is already add his attendance in NRS table or should I said if the LOG_IN in attendance table is equal to TIME_IN in NRS table the data will have a color yellow.
For Example:
I have this data in attendance table:
EMP_ID = 012012
LOG_IN = NULL
LOG_OUT = 2011-12-12 13:35:00
I will his attendance in NRS table to have his attendance:
EMP_NO = 012012
TIME_IN = 2011-12-12 05:35:00
TIME_OUT = 2011-12-12 13:35:00
In my above query the LOG_IN has a background color of RED.
I want to happen is if I add his attendance in NRS the EMP_NO, LOG_IN, LOGOUT will have a color to notice that it is already have in NRS.
Because theirs a scenario that the employee has no login or no logout or both.
Feel free to ask me if my explanation is not clear to you.
Thank you in advance
-
I already resolved it using this code:
function editloan(thedate){ var dateprocess = thedate //alert(dateprocess); window.location = "SSSLoan.php?dateprocess="+dateprocess; } -
Hi..
I encountered problem in using $_GET to get the DATE_PROCESS.
here is my code:
<script> function editloan(){ var dateprocess = document.getElementById('dateprocess').value; alert(dateprocess); window.location = "SSSLoan.php?dateprocess="+dateprocess; } </script><div id="searchname"> <form> <p class="serif"><b>Search Lastname:</b></p> <input type="text" name="ssssearch" size="20" onkeyup="fetchsuggest(this.value);"> <div> <hr /> <ul id="suggest" style="overflow:auto; height:380px; width:auto;"> {section name=co_emp loop=$personalAll} <li><a href="SSSgetdata.php?queryEmpID={$personalAll[co_emp].EMP_ID}">{$personalAll[co_emp].FULLNAME}</a></li> <hr /> {sectionelse} <li>No records found</li> {/section} </ul> </div> </div> <div id="loanformmain"> <input type="button" name="sssbtn" value="SSS" onclick="loanFrm()"> <input type="button" name="hdmfbtn" value="HDMF" onClick="hdmfloanFrm()"> <input type="button" name="UTbtn" value="Union Dues/Trust Fund" onclick="utloanFrm()"> </div> <div id="sssloan"> <fieldset> <legend>SSS Loan</legend> <p class="serif"> <label id="SSSLabel">SSS ID</label><label id="EMPIDLabel">EMP ID</label><label id="NAMELabel">NAME</label><label id="LOANLabel">LOAN</label><label id="AMORLabel">DEDUCTION</label> <input type="text" name="SSS" value="{$sss}" size="8" style="background: #e2e2e2" readonly="readonly"> <input type="text" name="EMP_NO" value="{$empno}" size="8" style="background: #e2e2e2" readonly="readonly"> <input type="text" name="NAME" value="{$fullname}" style="background: #e2e2e2" readonly="readonly" size="35"> <input type="text" name="LOAN" value="{$LOAN}" size="9"> <input type="text" name="AMOR" value="{$AMOR}" size="9"> <input type="button" name="add" value="ADD" onclick="SSSAdd()"> <input type="hidden" name="dateprocess" value="{$dateprocess"> </p> </legend> </fieldset> <div style="overflow:auto; height:300px; width:auto;"> <p> <table border="1" class="stat"> <tr> <td colspan="4" style="text-align:center">SSS ID</td> <td colspan="4" style="text-align:center">EMP ID</td> <td colspan="15" style="text-align:center">NAME</td> <td colspan="4" style="text-align:center">LOAN</td> <td colspan="4" style="text-align:center">DEDUCTION</td> <td colspan="4" style="text-align:center">DATE PROCESS</td> </tr> {section name=att loop=$getsss} <tr> <td colspan="4" style="background: #e2e2e2" readonly="readonly">{$getsss[att].SSS}</td> <td colspan="4" style="background: #e2e2e2" readonly="readonly">{$getsss[att].EMP_NO}</td> <td colspan="15" style="background: #e2e2e2" readonly="readonly">{$getsss[att].FULLNAME}</td> <td colspan="4" style="background: #e2e2e2" readonly="readonly">{$getsss[att].SSSLoan}</td> <td colspan="4" style="background: #e2e2e2" readonly="readonly">{$getsss[att].SSSAmor}</td> <td colspan="4" style="background: #e2e2e2" readonly="readonly" id="dateprocess" onclick="editloan('{$getsss[att].DATE_PROCESS}')">{$getsss[att].DATE_PROCESS}</td> </tr> {sectionelse} <tr><td colspan="1">No DATA</td></tr> {/section} </table> <table border="1"> <tr> <td colspan="4" style="text-align:center"><b>TOTAL:</b></td> <td colspan="5" style="background: #e2e2e2" readonly="readonly">{$Total_Loan}</td> </tr> </table> </p> </form> </div> </div><?php include 'config.php'; $currentEmpID = $_SESSION['empID']; $sql = "SELECT EMP_ID, CONCAT(LNAME, ', ', FNAME, ' ', MI, '.') AS FULLNAME, SSS, HDMF, TIN FROM PERSONAL WHERE EMP_ID='$currentEmpID'"; $recPersonal = $conn->Execute($sql); if (!$recPersonal) { print $conn->ErrorMsg(); } if (!$recPersonal->BOF) { $recPersonal->MoveFirst(); } $sss = trim($recPersonal->fields['SSS']); $hdmf = trim($recPersonal->fields['HDMF']); $tin = trim($recPersonal->fields['TIN']); $smarty->assign('sss', $sss); $sql = "SELECT EMP_ID, CONCAT(LNAME, ', ' , FNAME, ' ', MI) AS FULLNAME FROM PERSONAL ORDER BY LNAME ASC"; $recPersonalNav = $conn->GetAll($sql); $smarty->assign('personalAll', $recPersonalNav); // ======================================================================================================================== $sql = "SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ', FNAME, ' ', MI, '.') AS FULLNAME FROM PERSONAL p, EMPLOYMENT em WHERE p.EMP_ID='$currentEmpID' AND em.EMP_ID = '$currentEmpID'"; $recPersonalHead = $conn->Execute($sql); $fullName = $recPersonalHead->fields["FULLNAME"]; $empno = $recPersonalHead->fields["EMP_NO"]; $smarty->assign('empid', $currentEmpID); $smarty->assign('fullname', $fullName); $smarty->assign('empno', $empno); //===============================SELECT SSSLoan=================================== $dateprocess = $_GET['dateprocess']; $sql = "SELECT s.EMP_NO, s.SSSLoan, s.SSSAmor, s.DATE_PROCESS FROM $PAYROLL.sssloan s, $ADODB_DB.employment em WHERE em.EMP_NO= s.EMP_NO AND s.DATE_PROCESS = '$dateprocess'"; $rsloan = $conn2->Execute($sql); $LOAN = trim($rsloan->fields['SSSLoan']); $AMOR = trim($rsloan->fields['SSSAmor']); $dateprocess = $rsloan->fields['DATE_PROCESS'] ; $smarty->assign('LOAN', $LOAN); $smarty->assign('AMOR', $AMOR); $smarty->assign('dateprocess', $dateprocess); //============================SELECT ALL DATA FOR SSSLOAN========================== $sql = "SELECT s.EMP_NO, em.EMP_ID, p.SSS, CONCAT(LNAME, ', ', FNAME, ' ', MI, '.') AS FULLNAME, s.SSSLoan, s.SSSAmor, s.DATE_PROCESS FROM $ADODB_DB.PERSONAL p, $ADODB_DB.employment em, $PAYROLL.sssloan s WHERE s.EMP_NO = em.EMP_NO AND p.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID'"; $rs = $conn2->GetAll($sql); $smarty->assign('getsss', $rs); $sql = "SELECT s.EMP_NO, SUM(SSSAmor) AS Total_Loan FROM $PAYROLL.sssloan s, $ADODB_DB.employment em WHERE em.EMP_NO = s.EMP_NO AND em.EMP_ID = '$currentEmpID'" or die (mysql_error()); $rsTotal = $conn2->Execute($sql); $Total_Loan = $rsTotal->fields['Total_Loan']; $smarty->assign('Total_Loan', $Total_Loan); $smarty->display('header.tpl'); $smarty->display('loanForm.tpl'); $smarty->display('footer.tpl'); exit(); ?>when I click date in <td colspan="4" style="background: #e2e2e2" readonly="readonly" id="dateprocess" onclick="editloan('{$getsss[att].DATE_PROCESS}')">{$getsss[att].DATE_PROCESS}</td>
it result no value...it did not get the value that I click
Thank you in advance
how to use if then else in UPDATE Statement
in MySQL Help
Posted
Hi,
I have table: plan which has fields : PCODE, max_lot, min_lot
sample data:
PCODE = P35
max_lot = 5
min_lot = 3
PCODE = P35M
max_lot = 5
min_lot = 3
and table kanban_checker : PCODE, count_wip_chemicalweighing, count_wip_compounding, count_wip_extrusion, count_wip_forming, count_wip_deflashing, virtual, kanban and kanban_status....
I have this code of update to update kanban_checker table fields except kanban_status.
here is my code that I want to add update for kanban_status:
I need to check the max_lot and min_lot per PCODE in table plan for kanban_status..
IF virtual is >= max_lot then kanban_status = 'MAX' elseif virtual is <= min_lot then kanban_status = 'MIN'.
IS it possible?HOw?
Thank you