newphpbees

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: UPDATE kanban_checker kc SET count_wip_chemical_weighing = (SELECT COUNT(NULLIF(wip_chemicalweighing, 0)) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), count_wip_compounding = (SELECT COUNT(NULLIF(wip_compounding, 0)) AS count_wip_compounding FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), count_wip_extrusion = (SELECT COUNT(NULLIF(wip_extrusion, 0)) AS count_wip_extrusion FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), count_wip_forming = (SELECT COUNT(NULLIF(wip_forming, 0)) AS count_wip_forming FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), count_wip_deflashing = (SELECT COUNT(NULLIF(wip_deflashing, 0)) AS count_wip_deflashing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), virtual = (SELECT ((count(NULLIF(kd.wip_chemicalweighing, 0))) + (count(NULLIF(kd.wip_compounding, 0))) + (count(NULLIF(kd.wip_extrusion, 0))) + (count(NULLIF(kd.wip_forming, 0))) + (count(NULLIF(kd.wip_deflashing, 0)))) AS virtual FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE); 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

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 17584_.doc

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