<?php
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
date_default_timezone_set("Asia/Kolkata");
include('log_entry.php');
include_once('includes/config/config.php');
error_reporting(0);
function saveHighlightedParams($medical_exam_id, $highlight_color, $hiddenHighlighter)
{
$rows_highlighted = " DELETE FROM medical_examination_highlighted where medical_exam_id = '" . $medical_exam_id ."' ";
error_log("deleteHighlightedParams Query: " . $rows_highlighted);
if(!$result_highlight = mysqli_query($GLOBALS['conn'], $rows_highlighted)){
error_log("deleteHighlightedParams:" . mysqli_error($GLOBALS['conn']) . "Failed Query:" . $rows_highlighted);
}
$keys = array_keys($highlight_color);
error_log("array keys for highlighted param " . print_r($keys,true));
for($i=0; $i<sizeof($keys); $i++){
$color_query = "insert into medical_examination_highlighted set medical_exam_id='" . $medical_exam_id . "', highlighted_fields='" . $keys[$i] . "', highlighted_color ='".$highlight_color[$keys[$i]]."' ";
error_log("color query" . $color_query);
if (!$result = @mysqli_query($GLOBALS['conn'], $color_query)) {
error_log("saveHighlightedParams:" . mysqli_error($GLOBALS['conn']) . "Failed Query:" . $color_query);
exit(mysqli_error($GLOBALS['conn']));
}
}
}
function calculateHealthIndex($bp1,$bp2,$bmi,$sugar,$cholestrol){
$bp_score = 0;
$bmi_score = 0;
$sugar_score = 0;
$cholestrol_score = 0;
$total_score = 0;
if ($bp1 > 140 && $bp2 > 90) {
$bp_score = 4;
} else if ($bp1 > 120 && $bp1 < 140 && $bp2 > 80 && $bp2 < 90) {
$bp_score = 2;
} else {
$bp_score = 0;
}
if ($sugar > 200) {
$sugar_score = 4;
} else if ($sugar > 139 && $sugar < 200) {
$sugar_score = 2;
} else {
$sugar_score = 0;
}
if ($bmi > 30) {
$bmi_score = 4;
} else if ($bmi > 25 && $bmi < 30) {
$bmi_score = 2;
} else {
$bmi_score = 0;
}
if ($cholestrol > 240) {
$cholestrol_score = 4;
} else if ($cholestrol > 200 && $cholestrol < 240) {
$cholestrol_score = 2;
} else {
$cholestrol_score = 0;
}
$total_score =$bp_score +$bmi_score +$sugar_score + $cholestrol_score;
return$total_score;
}
function getBreadCrumbs()
{
$filename = basename($_SERVER['REQUEST_URI']);
$sql_root = "select distinct(m.menu_id),m.menu_name,m.menu_description,m.menu_url,m.parent_id,m.icon_text from assign_menu a, menu_master m where a.menu_id= m.menu_id and upper(m.menu_url) like upper('%" . $filename . "') and a.role_id='" . $_SESSION['RoleId'] . "' ";
// echo $sql_master;
$res_root = mysqli_query($GLOBALS['conn'], $sql_root);
$breadcrum_str = "";
$num_rows_root = @mysqli_num_rows($res_root);
if ($num_rows_root == 1) {
if ($root_rowmaster = @mysqli_fetch_array($res_root)) {
$child_last_menu_id = $root_rowmaster['menu_id'];
$child_last_menu_url = $root_rowmaster['menu_url'];
$child_last_menu_name = $root_rowmaster['menu_name'];
$child_last_parent_menuid = $root_rowmaster['parent_id'];
$breadcrum_str = "<li class='active'><a href='" . $child_last_menu_url . "'>" . $child_last_menu_name . "</a></li>" . $breadcrum_str;
if ($child_last_parent_menuid > 0) {
$sql_level1 = "select distinct(m.menu_id),m.menu_name,m.menu_description,m.menu_url,m.parent_id,m.icon_text from assign_menu a, menu_master m where a.menu_id= m.menu_id and cast(m.menu_id AS DECIMAL(12,2)) = cast('" . $child_last_parent_menuid . "' AS DECIMAL(12,2)) ";
// echo $sql_level1;
$res_level1 = mysqli_query($GLOBALS['conn'], $sql_level1);
$num_rows_level1 = @mysqli_num_rows($res_level1);
if ($num_rows_level1 == 1) {
if ($level1_rowmaster = @mysqli_fetch_array($res_level1)) {
$level1_menu_id = $level1_rowmaster['menu_id'];
$level1_menu_url = $level1_rowmaster['menu_url'];
$level1_menu_name = $level1_rowmaster['menu_name'];
$level1_parent_menuid = $level1_rowmaster['parent_id'];
$breadcrum_str = "<li class=''><a href='" . $level1_menu_url . "'>" . $level1_menu_name . "</a></li>" . $breadcrum_str;
if ($level1_parent_menuid > 0) {
$sql_level2 = "select distinct(m.menu_id),m.menu_name,m.menu_description,m.menu_url,m.parent_id,m.icon_text from assign_menu a, menu_master m where a.menu_id= m.menu_id and cast(m.menu_id AS DECIMAL(12,2)) = cast('" . $level1_parent_menuid . "' AS DECIMAL(12,2)) ";
// echo $sql_level2;
$res_level2 = mysqli_query($GLOBALS['conn'], $sql_level2);
$num_rows_level2 = @mysqli_num_rows($res_level2);
if ($num_rows_level2 == 1) {
if ($level2_rowmaster = @mysqli_fetch_array($res_level2)) {
$level2_menu_id = $level2_rowmaster['menu_id'];
$level2_menu_url = $level2_rowmaster['menu_url'];
$level2_menu_name = $level2_rowmaster['menu_name'];
$level2_parent_menuid = $level2_rowmaster['parent_id'];
$level2_icon_text = $level2_rowmaster['icon_text'];
$breadcrum_str = "<li class=''><a href='" . $level2_menu_url . "'><i class='" . $level2_icon_text . "'></i>" . $level2_menu_name . "</a></li>" . $breadcrum_str;
}
}
}
}
}
}
}
}
$breadcrum_str = "<ul class=\"breadcrumb\">" . $breadcrum_str . "</ul>";
return $breadcrum_str;
}
function isAccessible($roleId, $menu_key, $operationType)
{
if ($menu_key == null || $menu_key == '') {
$menu_key = $_SESSION['menu_key'];
error_log("menu key:".$menu_key);
}
error_log("menu key:".$menu_key);
$accessLevel = getaccesslevel($roleId, $menu_key);
// echo "accessLevel:".$accessLevel;
$accessLevelNumValue = ($accessLevel == 'E') ? 3 : (($accessLevel == 'W') ? 2 : (($accessLevel == 'R') ? 1 : 0));
// echo "accessLevelNumValue:".$accessLevelNumValue;
$operationTypeNumValue = ($operationType == 'E') ? 3 : (($operationType == 'W') ? 2 : (($operationType == 'R') ? 1 : 0));
// echo "operationTypeNumValue:".$operationTypeNumValue;
// echo $operationTypeNumValue<=$accessLevelNumValue;
if ($operationTypeNumValue <= $accessLevelNumValue) {
// echo "returning true";
return true;
} else {
// echo "returning false";
return false;
}
}
function getTodayProcurementCount()
{
$sql_division = "select count(procurement_id) count from procurement where DATE(procurement_date) = CURDATE() and ohc_type_id='".$_SESSION['current_ohcttype']."'";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIssueCount()
{
$sql_division = "select count(stock_issue_id) count from stock_issue where DATE(issue_date) = CURDATE() and issue_ohc_type_id='".$_SESSION['current_ohcttype']."'";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getExpiredMedCount()
{
$sql_division = "select count(id) count from cleanup_item_stock where ohc_type_id='".$_SESSION['current_ohcttype']."'";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getIndentCount()
{
$sql_division = "select count(indent_id) count from indent_master where year(indent_date)=year(NOW())";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTotalStockCount()
{
$sql_division = "select count(distinct item_id) count from item_stock where stock_qty>0 and ohc_type_id='".$_SESSION['current_ohcttype']."'";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayHealthSurvellianceCount()
{
$sql_division = "select count(checkup_id) count from checkup_form where is_test_completed='Y' and DATE(checkup_date) = CURDATE() and (current_status!='MRP' and current_status!='MRA') and ohc_type_id='".$_SESSION['current_ohcttype']."' ";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayLabSurvellianceCount(){
$sql_division_count = "select count(checkup_id) as count from checkup_form where DATE(checkup_date) = CURDATE() and current_status='MRA' and ohc_type_id='".$_SESSION['current_ohcttype']."' ";
$result_name_count = mysqli_query($GLOBALS['conn'],$sql_division_count);
if ($row_name_count = @mysqli_fetch_array($result_name_count)) {
return $row_name_count['count'];
}
}
function getTodayVendorRateApprovalCount(){
$user_id=($_SESSION['user_id']);
$contractor_id=getTableFieldValue('tbl_users','vendor_id','user_id',$user_id);
$sql_division_count = "select count((indent_id)) as count from indent_master where DATE(indent_date) = CURDATE() and status in ('APPROVED_LEVEL_2','PENDING_RATE','DRAFT_VENDOR_RATE') and indent_id in ( select indent_id from indent_items where vendor_id='$contractor_id' and status in ('',null) ) ";
$result_name_count = mysqli_query($GLOBALS['conn'],$sql_division_count);
if ($row_name_count = @mysqli_fetch_array($result_name_count)) {
return $row_name_count['count'];
}
}
function getpendingIndentCountLeve1(){
$sql_pending_indent_count = "select count(indent_id) as count from indent_master where status='APPROVED_LEVEL_1'";
$result_pending_indent_count = mysqli_query($GLOBALS['conn'],$sql_pending_indent_count);
if ($row_pending_indent_count = @mysqli_fetch_array($result_pending_indent_count)) {
return $row_pending_indent_count['count'];
}
}
function getpendingIndentCountLeve2(){
$sql_pending_indent_count = "select count(indent_id) as count from indent_master where status='APPROVED_LEVEL_2'";
$result_pending_indent_count = mysqli_query($GLOBALS['conn'],$sql_pending_indent_count);
if ($row_pending_indent_count = @mysqli_fetch_array($result_pending_indent_count)) {
return $row_pending_indent_count['count'];
}
}
function getcreatedIndentCountLeve2($status){
$sql_pending_indent_count = "select count(indent_id) as count from indent_master where indent_id in (select indent_id from indent_items where status='$status')";
error_log('count quotation'.$sql_pending_indent_count);
$result_pending_indent_count = mysqli_query($GLOBALS['conn'],$sql_pending_indent_count);
if ($row_pending_indent_count = @mysqli_fetch_array($result_pending_indent_count)) {
return $row_pending_indent_count['count'];
}
}
function getGRN(){
$sql_pending_indent_count = "select count(*) as count from indent_master where indent_id in (select DISTINCT(indent_id) from indent_items where status in ('DISPATCHE','PENDING_DISPATCH')) ";
$result_pending_indent_count = mysqli_query($GLOBALS['conn'],$sql_pending_indent_count);
if ($row_pending_indent_count = @mysqli_fetch_array($result_pending_indent_count)) {
return $row_pending_indent_count['count'];
}
}
function getTodayffiSurvellianceCount(){
$sql_division = "select count(response_id) count from questionaire_master_response where form_type='ffi' and DATE(screen_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodaydlfSurvellianceCount(){
$sql_division = "select count(response_id) coun from questionaire_master_response where form_type='dlf' and DATE(screen_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodaychsSurvellianceCount(){
$sql_division = "select count(response_id) count from questionaire_master_response where form_type='chs' and DATE(screen_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodaykhsSurvellianceCount(){
$sql_division = "select count(response_id) count from questionaire_master_response where form_type='khs' and DATE(screen_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getHealthSurvellianceCount()
{
$sql_division = "select count(checkup_id) count from checkup_form where current_status='Approved' and DATE(checkup_date) = CURDATE()";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getPending_doctor_HealthSurvellianceCount()
{
// $sql_division = "select count(checkup_id) count from checkup_form where is_test_completed!='Y'";
$sql_division = "SELECT count(checkup_id) FROM checkup_form where current_status='DRP' and DATE(checkup_date) = CURDATE()";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
error_log('pending' . print_r($row_name, true));
return $row_name['0'];
}
}
function getPending_Medical_HealthSurvellianceCount()
{
// $sql_division = "select count(checkup_id) count from checkup_form where is_test_completed!='Y'";
$sql_division = "SELECT count(checkup_id) FROM checkup_form where current_status='MDP' and DATE(checkup_date) = CURDATE() and ohc_type_id='".$_SESSION['current_ohcttype']."'";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
error_log('pending' . print_r($row_name, true));
return $row_name['0'];
}
}
function getPending_Lab_HealthSurvellianceCount(){
$sql_division = "SELECT count(checkup_id) FROM checkup_form where current_status='MRP' and DATE(checkup_date) = CURDATE() and ohc_type_id='".$_SESSION['current_ohcttype']."'";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
error_log('pending' . print_r($row_name, true));
return $row_name['0'];
}
}
function getPendingDISPATCHECount(){
$user_id=$_SESSION['user_id'];
error_log('xxxxxxxxx'.$user_id);
$contractor_id=getTableFieldValue('tbl_users','vendor_id','user_id',$user_id);
$sql_division = "SELECT count(indent_id) FROM indent_master where status not in ('DISPATCHE','REJECTED') and indent_id in (select distinct(indent_id) from indent_items where vendor_id='".$contractor_id."' and status in ('APPROVED','REJECTED','PENDING_DISPATCH','DRAFT_DISPATCH','GRN_APPROVED','COMPLETED')) ";
error_log('dispatch count'.$sql_division);
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
error_log('pending' . print_r($row_name, true));
return $row_name['0'];
}
}
function getYearlyApprovedIndentCount(){
$month_indent=date("m");
if($month_indent<=3)$year_indent=(date("Y")-1);
else $year_indent=date("Y");
$sql_division = "SELECT count(indent_id) FROM indent_master where status NOT IN ('draft','','PENDING','APPROVED_LEVEL_1') and DATE(indent_date) between STR_TO_DATE('01/04/" . $year_indent . "','%d/%m/%Y') and date(now())";
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
error_log('pending' . print_r($row_name, true));
return $row_name['0'];
}
}
function getYearlyApprovedIndentCountSuperviser(){
$month_indent=date("m");
if($month_indent<=3)$year_indent=(date("Y")-1);
else $year_indent=date("Y");
$sql_division = "SELECT count(indent_id) FROM indent_master where status NOT IN ('draft','','PENDING','APPROVED_LEVEL_1','APPROVED_LEVEL_2') and DATE(indent_date) between STR_TO_DATE('01/04/" . $year_indent . "','%d/%m/%Y') and date(now())";
error_log('pending' . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'],$sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['0'];
}
}
function getTodayTotalcase($type){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND form_type = '".$type."'";
error_log("total".$sql_division);
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_infection(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'N' AND f_status = 'DRP' AND form_type = 'ffi'";
error_log("ffi".$sql_division);
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getapproval_infection(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'Y' AND f_status = 'DRP' AND form_type = 'ffi'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_dlf(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'N' AND f_status = 'DRP' AND form_type = 'dlf'";
error_log("dlf pe".$sql_division);
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getapproval_dlf(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'Y' AND f_status = 'DRP' AND form_type = 'dlf'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_canteen(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'N' AND f_status = 'DRP' AND form_type = 'chs'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getapproval_canteen(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'Y' AND f_status = 'DRP' AND form_type = 'chs'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_kitchen(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'N' AND f_status = 'DRP' AND form_type = 'khs'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_indent(){
$sql_division = "SELECT count(*) FROM indent_master where DATE(indent_date) = CURDATE() AND status in ('PENDING','REJECTED') ";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getTodayapproval_indent(){
$sql_division = "SELECT count(*) FROM indent_master where DATE(indent_date) = CURDATE() ";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getPending_inventory_checklist_status(){
$sql_division = "SELECT count(*) FROM inventory_checklist_status where DATE(approve_date) = CURDATE() AND status = 'PENDING' and ohc_type_id='" . $_SESSION ['current_ohcttype'] . "'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getTodayapproval_inventory_checklist_status(){
$sql_division = "SELECT count(*) FROM inventory_checklist_status where DATE(approve_date) = CURDATE() and ohc_type_id='" . $_SESSION ['current_ohcttype'] . "' ";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getapproval_kitchen(){
$sql_division = "SELECT count(*) FROM questionaire_master_response where DATE(screen_date) = CURDATE() AND approval_status = 'Y' AND f_status = 'DRP' AND form_type = 'khs'";
$result = mysqli_query($GLOBALS['conn'],$sql_division);
$row= mysqli_fetch_row($result);
return $row[0];
}
function getaccesslevel($Role_id, $menu_id)
{
$sql_role = " select access_level from assign_menu where role_id='" . $Role_id . "' and menu_id= '" . $menu_id . "' ";
$res = mysqli_query($GLOBALS['conn'], $sql_role);
error_log("access level:".$sql_role);
$accesslevel = "";
if ($row = @mysqli_fetch_array($res)) {
@extract($row);
$accesslevel = $row['access_level'];
}
return $accesslevel;
}
function generate_options($sql, $selIndx = '', $option_id, $option_name, $chk_id = '', $dis)
{
$res = @mysqli_query($GLOBALS['conn'], $sql);
error_log("field by" . $sql);
while ($rows = @mysqli_fetch_array($res)) {
if ($chk_id == '' || $chk_id != $rows[$option_id]) {
if ($rows[$option_id] == $selIndx) {
echo "<option value='" . $rows[$option_id] . "' selected>" . $rows[$option_name] . "</option>";
} else if ($rows[$option_id] == '1' && $dis == 'div') {
echo "<option value='" . $rows[$option_id] . "' disabled>" . $rows[$option_name] . "</option>";
} else {
echo "<option value='" . $rows[$option_id] . "'>" . $rows[$option_name] . "</option>";
// error_log("echo "."<option value='" . $rows[$option_id] . "'>" . $rows[$option_name] . "</option>");
}
}
}
}
function getTreatmentText($appointment_id)
{
$sql = "select t.item_id, item_name, for_days, dosage from treatment t, tbl_items i where t.item_id=i.item_id and t.appointment_id='" . $appointment_id . "' and is_display!='N'";
$res = @mysqli_query($GLOBALS['conn'], $sql);
$returnStr = "";
while ($rows = @mysqli_fetch_array($res)) {
extract($rows);
$returnStr .= getItemWithFormName($rows['item_id']) . " " . $rows['dosage'] . " for " . $rows['for_days'] . " days" . "<br>";
}
return $returnStr;
}
function getTreatmentTextForGuest($appointment_id)
{
$sql = "select t.item_id, item_name, for_days, dosage from guest_treatment t, tbl_items i where t.item_id=i.item_id and t.appointment_id='" . $appointment_id . "'";
$res = @mysqli_query($GLOBALS['conn'], $sql);
$returnStr = "";
while ($rows = @mysqli_fetch_array($res)) {
extract($rows);
$returnStr .= $rows['item_name'] . " " . $rows['dosage'] . " for " . $rows['for_days'] . " days" . "<br>";
}
return $returnStr;
}
function getCommaSeperatedValuesForInClause($sql, $whereColforIn, $commaSeperatedValuesForIn)
{
$realArray = explode(',', $commaSeperatedValuesForIn);
$stringForIn = "'" . implode("','", $realArray) . "'";
$sql .= " where " . $whereColforIn . " in (" . $stringForIn . ")";
// return $sql;
$res = @mysqli_query($GLOBALS['conn'], $sql);
$returnStr = "";
while ($rows = @mysqli_fetch_array($res)) {
extract($rows);
$returnStr .= $rows[0] . ", ";
}
if (strlen($returnStr) == 0) {
return $returnStr;
}
return substr($returnStr, 0, strlen($returnStr) - 2);
}
function getCommaSeperatedValuesToInClause($commaSeperatedValuesForIn)
{
$realArray = explode(',', $commaSeperatedValuesForIn);
$stringForIn = "'" . implode("','", $realArray) . "'";
$returnStr = " (" . $stringForIn . ")";
return $returnStr;
}
function generate_Firm($sql, $selIndx = '', $option_id, $option_name, $chk_id = '')
{
$res = mysqli_query($GLOBALS['conn'], $sql);
while ($rows = mysqli_fetch_array($res)) {
if ($chk_id == '' || $chk_id != $rows[$option_id]) {
if ($rows[$option_id] == $selIndx) {
echo "<option value='" . $rows[$option_id] . "' >" . $rows[$option_name] . "</option>";
} else {
echo "<option value='" . $rows[$option_id] . "'>" . $rows[$option_name] . "</option>";
}
}
}
}
// ***************************** FUNCTION TO CONVERT DATE FROM ONE FORMAT TO ANOTHER ************************//
function date_conversion($date_str, $sourse_format, $desired_format)
{
$source_day = "";
$source_month = "";
$source_year = "";
switch ($sourse_format) {
case "Y-m-d":
$sourse_arr = split('[-/]', $date_str);
$source_day = $sourse_arr[2];
$source_month = $sourse_arr[1];
$source_year = $sourse_arr[0];
break;
case "y-m-d":
$sourse_arr = split('[-/]', $date_str);
$source_day = $sourse_arr[2];
$source_month = $sourse_arr[1];
$source_year = $sourse_arr[0];
break;
case "d-m-Y":
$sourse_arr = @split('[-/]', $date_str);
$source_day = $sourse_arr[0];
$source_month = $sourse_arr[1];
$source_year = $sourse_arr[2];
break;
case "d-m-Y":
$sourse_arr = @split('[-/]', $date_str);
$source_day = $sourse_arr[0];
$source_month = $sourse_arr[1];
$source_year = $sourse_arr[2];
break;
case "m-d-Y":
$sourse_arr = split('[-/]', $date_str);
$source_day = $sourse_arr[1];
$source_month = $sourse_arr[0];
$source_year = $sourse_arr[2];
break;
}
if ($source_month >= 1 && $source_day >= 1 && $source_year >= 1970) {
$result_date = date($desired_format, mktime(0, 0, 0, $source_month, $source_day, $source_year));
} else {
$result_date = "";
}
return $result_date;
}
// *************************************** END FUNCTION *****************************************************//
function generateOptionForMultiple($tableName, $nameCol, $idCol, $selectedId, $dis)
{
$sql = "select $nameCol,$idCol from $tableName order by $nameCol ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
$selectedIdArray = explode(",", $selectedId);
$flag = 0;
for ($i = 0; $i < count($selectedIdArray); $i++) {
if ($row[$idCol] == $selectedIdArray[$i]) {
$flag = 1;
}
}
if ($flag == 1) {
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$nameCol] . "</option>";
} else {
$optionValue .= "<option value='" . $row[$idCol] . "' >" . $row[$nameCol] . "</option>";
}
}
return $optionValue;
}
function generateOption($tableName, $nameCol, $idCol, $selectedId, $dis)
{
$sql = "select $nameCol,$idCol from $tableName order by $nameCol ";
error_log('xxx'.$selectedId);
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$nameCol] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled >" . $row[$nameCol] . "</option>";
} else
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
function generateOptionWithWhereClause($tableName, $nameCol, $idCol, $selectedId, $dis, $whereCol, $whereVal)
{
$sql = "select $nameCol,$idCol from $tableName where $whereCol=$whereVal order by $nameCol ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$nameCol] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled >" . $row[$nameCol] . "</option>";
} else
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
function generateOptionWithWhereClauseWithIn($tableName, $nameCol, $idCol, $selectedId, $dis, $whereCol, $whereVal)
{
$sql = "select $nameCol,$idCol from $tableName where $whereCol in $whereVal order by $nameCol ";
error_log($whereVal.'fun query'.$sql);
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$nameCol] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled >" . $row[$nameCol] . "</option>";
} else
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
function generateOptionWithWhereClause1($tableName, $nameCol, $idCol, $selectedId, $dis, $whereCol, $whereVal)
{
$sql = "select $nameCol,$idCol from $tableName where $whereCol='" . $whereVal . "' order by $nameCol ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
// error_log("fhshrfhfhsrhshr" . $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$nameCol] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled >" . $row[$nameCol] . "</option>";
} else
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
function generateCheckupOptionsAccToOhc($tableName, $nameCol, $idCol, $selectedId, $dis,$whereCol,$whereVal, $sessionOHCTypeId)
{
$sql = "SELECT $nameCol, $idCol, ohc_ids FROM $tableName WHERE FIND_IN_SET('$sessionOHCTypeId', ohc_ids) > 0 and $whereCol = '$whereVal' ORDER BY $nameCol";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
error_log("generate checkup query ".$sql);
$optionValue = ''; // Initialize the option values
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId) {
$optionValue .= "<option value='" . $row[$idCol] . "' selected='selected'>" . $row[$nameCol] . "</option>";
} else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled>" . $row[$nameCol] . "</option>";
} else {
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$nameCol] . "</option>";
}
}
return $optionValue;
}
function generateMedicineOptions($selectedId, $dis)
{
$sql = "SELECT item_name,form_name, item_id from tbl_items i, medicine_form m where i.item_form_id=m.form_id order by m.form_name ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row['item_id'] == $selectedId)
$optionValue .= "<option value='" . $row['item_id'] . "' selected=selected>" . $row['form_name'] . " " . $row['item_name'] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row['item_id'] . "' disabled >" . $row['form_name'] . " " . $row['item_name'] . "</option>";
} else
$optionValue .= "<option value='" . $row['item_id'] . "'>" . $row['form_name'] . " " . $row['item_name'] . "</option>";
}
return $optionValue;
}
function generateOptForVender($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select $nameCol,$idCol from $tableName where purchase='Y' order by $nameCol asc";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value=" . $row[$idCol] . " selected=selected>" . $row[$nameCol] . "</option>";
else
$optionValue .= "<option value=" . $row[$idCol] . " >" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
function generateDetailOption($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select $nameCol,$idCol from $tableName order by $idCol asc ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value=" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . " selected=selected>" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . "</option>";
else
$optionValue .= "<option value=" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . ">" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . "</option>";
}
return $optionValue;
}
function generateDetailOptionForCode($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select $nameCol,$idCol from $tableName ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
$optionValue .= "<option >" . preg_replace('/[^a-zA-Z0-9\']+/', "", $row[$nameCol]) . "</option>";
}
return $optionValue;
}
function getDetailOption($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select $nameCol,$idCol from $tableName order by $idCol asc ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value=" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . " selected=selected>" . preg_replace('/[\r\n]+/', "", $row[$nameCol]) . "</option>";
else
$optionValue .= "<option value=" . preg_replace('/[\r\n]+/', "", $row[$idCol]) . ">" . preg_replace('/[\r\n]+/', "", $row[$nameCol]) . "</option>";
}
return $optionValue;
}
function generateItemOptions($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select item_id, item_desc from tbl_items order by item_desc asc";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
$str_desc = $row['item_desc'];
list($item_name, $mat, $submat, $spec) = split("-", $str_desc);
$sql_mat = "select cat_name from tbl_categories where cat_id='" . $mat . "'";
$rs_mat = mysqli_query($GLOBALS['conn'], $sql_mat);
$row_mat = mysqli_fetch_array($rs_mat);
$mat = $row_mat['cat_name'];
$sql_smat = "select cat_name from tbl_categories where cat_id='" . $submat . "'";
$rs_smat = mysqli_query($GLOBALS['conn'], $sql_smat);
$row_smat = mysqli_fetch_array($rs_smat);
$submat = $row_smat['cat_name'];
if ($submat != "") {
$submat = "-" . $submat;
}
$item_name = htmlentities($item_name);
$spec = htmlentities($spec);
$str_desc = $item_name . "-" . $spec;
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value=" . $row[$idCol] . " selected=selected>" . $str_desc . "</option>";
else
$optionValue .= "<option value=" . $row[$idCol] . " >" . $str_desc . "</option>";
}
return $optionValue;
}
function has_amendments($po_no)
{
$sql_has_amendments = "select count(amendment_no) as amendment_no from purchase_order_master_amendment where purchase_order_no='" . $po_no . "' ";
$res_has_amendments = @mysqli_query($GLOBALS['conn'], $sql_has_amendments);
$num_has_amendments = @mysqli_fetch_array($res_has_amendments);
if ($num_has_amendments['amendment_no'] > 0) {
return true;
} else {
return false;
}
}
function getLatestAmendmentNo($po_no)
{
$sql_has_amendments = "select max(amendment_no) as amendment_no from purchase_order_master_amendment where purchase_order_no='" . $po_no . "'";
$res_has_amendments = @mysqli_query($GLOBALS['conn'], $sql_has_amendments);
if ($row = @mysqli_fetch_array($res_has_amendments)) {
return $row['amendment_no'];
} else {
return "NIL";
}
}
function getDrawingDetail($drawing_id)
{
$sql = "select drawing_details from tbl_drawing_detail where drawing_id='" . $drawing_id . "'";
$res = @mysqli_query($GLOBALS['conn'], $sql);
if ($row = @mysqli_fetch_array($res)) {
return $row['drawing_details'];
} else {
return "NIL";
}
}
function marked_qty($po_no = '', $item_desc = '', $id = '')
{
$sql_bal = "SELECT sum(qty_offered) as qty_offered from order_item_marking where (po_no='" . $po_no . "' and item_desc='" . addslashes($item_desc) . "') or id ='" . $id . "'";
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
extract($row_bal);
if ($qty_offered) {
return $qty_offered;
} else {
return 0;
}
}
}
function runSql($sql)
{
$result_bal = mysqli_query($GLOBALS['conn'], $sql);
if ($result_bal)
if ($row_bal = @mysqli_fetch_array($result_bal)) {
@extract($row_bal);
if ($qty_produced) {
echo $qty_produced;
} else {
// echo '0';
}
}
}
function getTableFieldValue($table_name, $field_name, $where1 = '', $val1 = '', $where2 = '', $val2 = '', $where3 = '', $val3 = '', $where4 = '', $val4 = '', $where5 = '', $val5 = '')
{
$sql_bal = "SELECT " . $field_name . " as field_value from " . $table_name;
$where_clause = " where ";
if ($where1 != '') {
$where_clause .= " " . $where1 . " = " . $val1;
}
if ($where2 != '') {
$where_clause .= " and " . $where2 . " = " . $val2;
}
if ($where3 != '') {
$where_clause .= " and " . $where3 . " = " . $val3;
}
if ($where4 != '') {
$where_clause .= " and " . $where4 . " = " . $val4;
}
if ($where5 != '') {
$where_clause .= " and " . $where5 . " = " . $val5;
}
if (strlen($where_clause) > 8) {
$sql_bal .= $where_clause;
}
error_log("sql ".$sql_bal);
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($result_bal) {
if ($row_bal = @mysqli_fetch_array($result_bal)) {
extract($row_bal);
if ($field_value) {
return $field_value;
} else {
return "";
}
}
} else {
return "";
}
}
function getFieldFromTable($field, $tableName, $clause, $clause_value)
{
$sql_bal = "SELECT " . $field . " as field_value from " . $tableName . " where " . $clause . "='" . $clause_value . "'";
// echo $sql_bal;
// error_log('getFieldFromTable: '.$sql_bal);
// return $sql_bal;
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
@extract($row_bal);
if ($field_value) {
// error_log('getFieldFromTable return value: '.$field_value);
return $field_value;
} else {
// error_log('getFieldFromTable return value: Nothing');
return "";
}
}
}
function generateOpt1($tableName, $nameCol, $selectedId)
{
$sql = "select $nameCol from $tableName group by $nameCol";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$nameCol] == $selectedId)
$optionValue .= "<option value=" . $row[$nameCol] . " selected=selected>" . $row[$nameCol] . "</option>";
else
$optionValue .= "<option value='" . $row[$nameCol] . "' >" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
$nwords = array(
"",
"one",
"two",
"three",
"four",
"five",
"six",
"seven",
"eight",
"nine",
"ten",
"eleven",
"twelve",
"thirteen",
"fourteen",
"fifteen",
"sixteen",
"seventeen",
"eightteen",
"nineteen",
"twenty",
0 => "zero",
30 => "thirty",
40 => "fourty",
50 => "fifty",
60 => "sixty",
70 => "seventy",
80 => "eigthy",
90 => "ninety"
);
function number_to_words($x)
{
global $nwords;
if (!is_numeric($x)) {
$w = '#';
} else {
$x = floor($x);
if ($x < 0) {
$w = 'minus ';
$x = -$x;
} else {
$w = '';
}
if ($x < 21) {
$w .= $nwords[$x];
} else if ($x < 100) {
$w .= $nwords[10 * floor($x / 10)];
$r = fmod($x, 10);
if ($r > 0) {
$w .= ' ' . $nwords[$r];
}
} else if ($x < 1000) {
$w .= $nwords[floor($x / 100)] . ' hundred';
$r = fmod($x, 100);
if ($r > 0) {
$w .= ' ' . number_to_words($r);
}
} else if ($x < 1000000) {
$w .= number_to_words(floor($x / 1000)) . ' thousand';
$r = fmod($x, 1000);
if ($r > 0) {
$w .= ' ';
if ($r < 100) {
$w .= ' ';
}
$w .= number_to_words($r);
}
} else {
$w .= number_to_words(floor($x / 1000000)) . ' million';
$r = fmod($x, 1000000);
if ($r > 0) {
$w .= ' ';
if ($r < 100) {
$word .= ' ';
}
$w .= number_to_words($r);
}
}
}
return $w;
}
function number_to_rupees($chk)
{
$rs = number_to_words($chk);
$paise = strstr($chk, '.') * 100;
$ps = number_to_words($paise);
if ($paise > 0) {
$final = "(Rupees " . $rs . ", paise " . $ps . " only)";
} else {
$final = "(Rupees " . $rs . " only)";
}
return $final;
}
function getUserInfo($user_id)
{
$sql_bal = "SELECT user_name from tbl_users where user_id='" . $user_id . "'";
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
return $row_bal['user_name'];
}
}
function getFirmInfo($firm_id)
{
$sql_bal = "SELECT firm_name from tbl_firms where firm_id='" . $firm_id . "'";
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
return $row_bal['firm_name'];
}
}
function getUnitName($unit_id)
{
$sql_bal = "SELECT unit_name from unit_master where unit_id='" . $unit_id . "'";
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
return $row_bal['unit_name'];
}
}
function getItemName($item_id)
{
$sql_bal = "SELECT item_name from tbl_items where item_id='" . $item_id . "'";
$result_bal = mysqli_query($GLOBALS['conn'], $sql_bal);
if ($row_bal = @mysqli_fetch_array($result_bal)) {
return $row_bal['item_name'];
}
}
function getAccessView($category)
{
$sql = "SELECT acc_view FROM rights_access where user_id='" . $_SESSION['user_id'] . "' and category='$category'";
$res = mysqli_query($GLOBALS['conn'], $sql);
if ($res) {
if ($row = @mysqli_fetch_array($res))
return $row['acc_view'];
} else {
return "";
}
}
function generateOption1($tableName, $nameCol, $idCol, $selectedId)
{
$sql = "select $nameCol,$idCol from $tableName ORDER BY $nameCol ASC";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value=" . $row[$idCol] . " selected=selected>" . $row[$nameCol] . "</option>";
else
$optionValue .= "<option value=" . $row[$idCol] . " >" . $row[$nameCol] . "</option>";
}
return $optionValue;
}
// to be used for purchase order view/delete/update onwards for filtering yearwise data display
//
function roman($arabic)
//
{
//
$fractions = array(
"",
"•",
"••",
"•••",
"••••",
"•••••",
"S",
"S•",
"S••",
"S•••",
"S••••",
"S•••••",
"I"
);
//
$ones = array(
"",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX"
);
//
$tens = array(
"",
"X",
"XX",
"XXX",
"XL",
"L",
"LX",
"LXX",
"LXXX",
"XC"
);
//
$hundreds = array(
"",
"C",
"CC",
"CCC",
"CD",
"D",
"DC",
"DCC",
"DCCC",
"CM"
);
//
$thousands = array(
"",
"M",
"MM",
"MMM",
"MMMM"
);
//
//
if ($arabic > 4999)
//
{
//
// For large numbers (five thousand and above), a bar is placed above a base numeral to indicate multiplication by 1000.
//
// Since it is not possible to illustrate this in plain ASCII, this function will refuse to convert numbers above 4999.
//
die("Cannot represent numbers larger than 4999 in plain ASCII.");
//
} //
elseif ($arabic == 0)
//
{
//
// About 725, Bede or one of his colleagues used the letter N, the initial of nullae,
//
// in a table of epacts, all written in Roman numerals, to indicate zero.
//
return "N";
//
} //
else //
{
//
// Handle fractions that will round up to 1.
//
if (round(fmod($arabic, 1) * 12) == 12)
//
{
//
$arabic = round($arabic);
//
}
//
//
// With special cases out of the way, we can proceed.
//
// NOTE: modulous operator (%) only supports integers, so fmod() had to be used instead to support floating point.
//
$roman = $thousands[($arabic - fmod($arabic, 1000)) / 1000];
//
$arabic = fmod($arabic, 1000);
//
$roman .= $hundreds[($arabic - fmod($arabic, 100)) / 100];
//
$arabic = fmod($arabic, 100);
//
$roman .= $tens[($arabic - fmod($arabic, 10)) / 10];
//
$arabic = fmod($arabic, 10);
//
$roman .= $ones[($arabic - fmod($arabic, 1)) / 1];
//
$arabic = fmod($arabic, 1);
//
//
// Handling for fractions.
//
if ($arabic > 0)
//
{
//
$roman .= $fractions[round($arabic * 12)];
//
}
//
//
return $roman;
//
}
//
}
// function for checking access
function checkaccesslevel($access_level, $user_acn)
{
if (!isset($user_acn) || $user_acn == '' || $access_level == 'E') { //removing delete access temporarily for all users
return;
}
$access_level = getaccesslevel($_SESSION['RoleId'], $menu_id);
// echo "access_level=".$access_level.",Menu_id:".$menu_id."useracn=".$user_acn;
if ((($access_level == 'R' || $access_level == 'W' || $access_level == 'E') && ($user_acn == 'view' || $user_acn == '')) || (($access_level == 'W' || $access_level == 'E') && $user_acn == 'update') || ($access_level == 'E' && $user_acn == 'delete')) {
return;
} else {
// echo "<script>location.replace('accessdenied.php')</script>";
}
}
function acceseLevel($user_id)
{
$sql = "select a.access_level from assign_menu a left join tbl_users b on a.role_id=b.role_id where b.user_id='$user_id'";
$res = @mysqli_query($GLOBALS['conn'], $sql);
$row = @mysqli_fetch_array($res);
$access_level = $row['access_level'];
return $access_level;
}
function acceseLevel_change($user_id, $pagekey)
{
$sql = "SELECT a.access_level
FROM assign_menu a
LEFT JOIN tbl_users b ON a.role_id = b.role_id
WHERE b.user_id = '" . $user_id . "'
AND a.menu_id = '" . $pagekey . "'";
$res = @mysqli_query($GLOBALS['conn'], $sql);
$row = @mysqli_fetch_array($res);
$access_level = $row['access_level'];
return $access_level;
}
function getDivisionTypeById($divisionId)
{
$sql_division = "select division_name from divisions where division_id='" . $divisionId . "'";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['division_name'];
}
}
function getTodayOpdCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='O' and DATE(appointment_date) = CURDATE() and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
error_log("row count:" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayVisitorCount()
{
$sql_division = "select count(appointment_id) from visitors_employee_appointment where DATE(appointment_date) = CURDATE() and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
error_log($sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name[0];
}
}
function visitorOpdCount()
{
$sql_division = "select count(appointment_id) count from visitors_employee_appointment where appointment_type='VO' and DATE(appointment_date) = CURDATE() and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPEMECount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='peme' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPEMEShortCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='peme_short' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPOHCHalfCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='pohc_half' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPOHCCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='pohc' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayFoodHandCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='foodhand_exam' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayEyeCheckCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='eye_check' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayCovidCount()
{
$sql_division = "select count(test_id) count from covid_screening_details where DATE(test_date) = CURDATE()";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayCovidMonitoringCount()
{
$sql_division = "select count(id) as count from covid_monitoring where DATE(date_added) = CURDATE()";
error_log("message" . $sql_division);
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayWahCount()
{
$sql_division = "select count(wah_id) count from work_at_height_details_new where DATE(last_modified) = CURDATE()";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPMECountByMedical()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='pme' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPMECountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='pme' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMELongTermCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where task='ime_long' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMELongTermCountByMedical()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='ime_long' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMELongTermCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='ime_long' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMEShortTermCount()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where task='ime_short' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMEShortTermCountByMedical()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='ime_short' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayIMEShortTermCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='ime_short' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPEMECountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='peme' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPEMEShortCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='peme_short' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPOHCHalfCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='pohc_half' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayPOHCCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='pohc' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayFoodHandCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='foodhand_exam' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayEyeCheckCountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='eye_check' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEAbove40Count()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where task='ame_greater_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEAbove40CountByMedical()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='ame_greater_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEAbove40CountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='ame_greater_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEBelow40Count()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where task='ame_less_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEBelow40CountByMedical()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='D' and task='ame_less_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayAMEBelow40CountByDoctor()
{
$sql_division = "select count(medical_exam_id) count from medical_examination where forward_status='A' and task='ame_less_40' and DATE(medical_entry_date) = CURDATE() and ohc_location_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayOpdPendingCosultationCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='O' and DATE(appointment_date) = CURDATE() and attended_status='DRP' and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayOpdPendingPharamacyCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='O' and DATE(appointment_date) = CURDATE() and attended_status='PHP' and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayVisitorOpdPendingPharamacyCount()
{
$sql_division = "select count(appointment_id) count from visitors_employee_appointment where appointment_type='VO' and DATE(appointment_date) = CURDATE() and attended_status='PHP' and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayInjuryCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='I' and DATE(appointment_date) = CURDATE() and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayVisitorInjuryCount()
{
$sql_division = "select count(appointment_id) count from visitors_employee_appointment where appointment_type='I' and DATE(appointment_date) = CURDATE() and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayInjuryPendingCosultationCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='I' and DATE(appointment_date) = CURDATE() and doctor_attended_flag='N' and attended_status='DRP' and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayInjuryPendingPharamacyCount()
{
$sql_division = "select count(appointment_id) count from employee_appointment where appointment_type='I' and DATE(appointment_date) = CURDATE() and attended_status='PHP' and ohc_type_id='" . $_SESSION['current_ohcttype'] . "' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayGuestCount()
{
$sql_division = "select count(appointment_id) count from guest_appointment where DATE(appointment_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodayCheckupCount()
{
$sql_division = "select count(checkup_id) count from checkup_form where DATE(checkup_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getTodaySicknessCount()
{
$sql_division = "select count(sickness_id) count from sickness where DATE(sickness_date) = CURDATE() ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getCurrentShift()
{
$sql_division = "select * from shift_status_details where current_status='R' and ohc_location_id='" . $_SESSION['current_ohcttype'] . "'";
$result_name = @mysqli_query($GLOBALS['conn'], $sql_division);
$row_name = @mysqli_fetch_assoc($result_name);
$shift = getFieldFromTable('status_name', 'shift_status', 'shift_status_id', $row_name['shift_id']);
if ($shift == '' || $shift == null) {
return $shift = "NO SHIFT";
} else {
return $shift;
}
}
function getTodaySicknessCountByMedical()
{
$sql_division = "select count(sickness_id) count from sickness where DATE(sickness_date) = CURDATE() AND attended_status !='DRA' ";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function getGroupItems($group_id)
{
$sql_for_group_items = "select * from group_items where group_id=$group_id ";
// echo $sql_for_group_items;
$result_for_group_items = mysqli_query($GLOBALS['conn'], $sql_for_group_items);
return $result_for_group_items;
}
function updateGroupItemFlagByItemId($item_id, $group_item_status)
{
$sql_update_group_item_flag = " update tbl_items set is_group_item='$group_item_status' where item_id=$item_id";
mysqli_query($GLOBALS['conn'], $sql_update_group_item_flag);
}
function updatePackagingItemFlagByItemId($item_id, $packaging_item_status)
{
$sql_update_group_item_flag = " update tbl_items set is_packaging_item='$packaging_item_status' where item_id=$item_id";
mysqli_query($GLOBALS['conn'], $sql_update_group_item_flag);
}
function updateTable($table, $field_name, $field_value, $cond, $value)
{
$sql_update_group_item_flag = " update $table set $field_name='$field_value' where $cond=$value";
// echo $sql_update_group_item_flag;
mysqli_query($GLOBALS['conn'], $sql_update_group_item_flag);
}
function updateItemStockAtDispensaryLevel($ohc_location, $item_id, $issue_qty)
{
// echo $ohc_location;
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
// echo $is_group_item;
if ($is_group_item == 'P') {
$result_for_group_items = getGroupItems($item_id);
// echo "shu". mysqli_num_rows($result_for_group_items);
while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
$used_qty = $row_for_group_items['qty'];
resetItemStockAtDispensaryLevelForGroupItems(-$used_qty, $row_for_group_items['item_id'], $ohc_location);
}
} else {
// $item_batch_no=getMinExpiaryDateItem($ohc_location, $item_id);
$current_stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location);
// echo $current_stock_qty;
$stock_qty = floatval($current_stock_qty) - (floatval($issue_qty));
// echo $stock_qty;
// echo $issue_qty ;
$current_stock_query = "update item_stock_dispensary set stock_qty='" . $stock_qty . "' where item_id='" . $item_id . "' and ohc_location_id=$ohc_location ";
// echo $current_stock_query;
if (!mysqli_query($GLOBALS['conn'], $current_stock_query)) {
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
function updateItemStockAtStoreLevel($ohc_location, $item_id, $issue_qty,$item_batch_no)
{
// echo $ohc_location;
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
// echo $is_group_item;
if ($is_group_item == 'P') {
$result_for_group_items = getGroupItems($item_id);
// echo "shu". mysqli_num_rows($result_for_group_items);
while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
$used_qty = $row_for_group_items['qty'];
resetItemStockAtStoreLevelForGroupItems(-$used_qty, $row_for_group_items['item_id'], $ohc_location,$item_batch_no);
}
} else {
// $item_batch_no=getMinExpiaryDateItem($ohc_location, $item_id);
$current_stock_qty = getStockQtyAtStoreLevel($item_id, $ohc_location,$item_batch_no);
// echo $current_stock_qty;
$stock_qty = floatval($current_stock_qty) - (floatval($issue_qty));
// echo $stock_qty;
// echo $issue_qty ;
$current_stock_query = "update item_stock set stock_qty='" . $stock_qty . "' where item_id='" . $item_id . "' and ohc_type_id=$ohc_location and item_batch_no='$item_batch_no' ";
// echo $current_stock_query;
if (!mysqli_query($GLOBALS['conn'], $current_stock_query)) {
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
function resetItemStockAtDispensaryLevelForGroupItems($used_qty, $item_id, $ohc_location_id)
{
// echo "used_qty".$used_qty."item_id".$item_id."location".$ohc_location_id;
// $batch_no=getMinExpiaryDateItem($ohc_location_id,$item_id);
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($used_qty);
// echo $batch_no;
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty=$total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
// echo $query_for_reset_item_dispensary_stock;
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock)) {
error_log("Failed to execute query_for_reset_item_dispensary_stock. Failing query:" . $query_for_reset_item_dispensary_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
function resetItemStockAtStoreLevelForGroupItems($used_qty, $item_id, $ohc_location_id,$item_batch_no)
{
// echo "used_qty".$used_qty."item_id".$item_id."location".$ohc_location_id;
// $batch_no=getMinExpiaryDateItem($ohc_location_id,$item_id);
$stock_qty = getStockQtyAtStoreLevel($item_id, $ohc_location_id,$item_batch_no);
$total_qty = floatval($stock_qty) + floatval($used_qty);
// echo $batch_no;
$query_for_reset_item_Store_stock = " update item_stock set stock_qty=$total_qty where item_id=$item_id and ohc_type_id=$ohc_location_id and item_batch_no='$item_batch_no' ";
// echo $query_for_reset_item_dispensary_stock;
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_Store_stock)) {
error_log("Failed to execute query_for_reset_item_Store_stock. Failing query:" . $query_for_reset_item_Store_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
function resetItemStockAtDispensaryLevel($appointment_id, $ohc_location_id)
{
$query_for_treatment = " select issued_qty,item_id from treatment where appointment_id='" . $appointment_id . "' ";
error_log("query_for_treatment:" . $query_for_treatment);
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['item_id'];
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
if ($is_group_item == 'C') {
resetItemStockAtDispensaryLevelForGroupItems($issued_qty, $item_id, $ohc_location_id);
/*
* $result_for_group_items = getGroupItems($item_id);
*
* while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
*
* $used_qty = $row_for_group_items['qty'];
*
* $item_id = $row_for_group_items['item_id'];
*
* resetItemStockAtDispensaryLevelForGroupItems($used_qty, $item_id, $ohc_location_id);
*
* }
*/
} else if ($is_group_item == 'N') {
// $batch_no = getMinExpiaryDateItem($ohc_location_id, $item_id);
// echo "b".$batch_no;
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
error_log("stock_qty:" . $stock_qty);
error_log("issued_qty:" . $issued_qty);
error_log("total_qty:" . $total_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty= $total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
error_log("query_for_reset_item_dispensary_stock:" . $query_for_reset_item_dispensary_stock);
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock)) {
error_log("failed to reset dispensary stock:" . mysqli_error($GLOBALS['conn']) . " Failing Query:" . $query_for_reset_item_dispensary_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
}
function updaterItemStock($ohc, $item, $qty,$batch){
if($_SESSION['RoleCode']=='DIS'){
updateItemStockAtDispensaryLevel($ohc, $item, $qty);
}else{
updateItemStockAtStoreLevel($ohc, $item, $qty,$batch);
}
}
function resetItemStockAtStoreLevel($appointment_id, $ohc_location_id)
{
$query_for_treatment = " select issued_qty,item_id,item_batch_no from treatment where appointment_id='" . $appointment_id . "' ";
error_log("query_for_treatment:" . $query_for_treatment);
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['item_id'];
$item_batch_no = $row_for_treatemnt['item_batch_no'];
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
if ($is_group_item == 'C') {
resetItemStockAtStoreLevelForGroupItems($issued_qty, $item_id, $ohc_location_id,$item_batch_no);
/*
* $result_for_group_items = getGroupItems($item_id);
*
* while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
*
* $used_qty = $row_for_group_items['qty'];
*
* $item_id = $row_for_group_items['item_id'];
*
* resetItemStockAtStoreLevelForGroupItems($used_qty, $item_id, $ohc_location_id);
*
* }
*/
} else if ($is_group_item == 'N') {
// $batch_no = getMinExpiaryDateItem($ohc_location_id, $item_id);
// echo "b".$batch_no;
$stock_qty = getStockQtyAtStoreLevel($item_id, $ohc_location_id,$item_batch_no);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
error_log("stock_qty:" . $stock_qty);
error_log("issued_qty:" . $issued_qty);
error_log("total_qty:" . $total_qty);
error_log("Batch:" . $item_batch_no);
$query_for_reset_item_Store_stock = " update item_stock set stock_qty= $total_qty where item_id=$item_id and ohc_type_id=$ohc_location_id and item_batch_no='$item_batch_no' ";
error_log("query_for_reset_item_Store_stock:" . $query_for_reset_item_Store_stock);
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_Store_stock)) {
error_log("failed to reset Store stock:" . mysqli_error($GLOBALS['conn']) . " Failing Query:" . $query_for_reset_item_Store_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
}
function resetIPDDispensaryItemsStock($ipd_id, $ohc_location_id)
{
$query_for_treatment = " select issued_qty,medicine from detention_intake where det_id='" . $ipd_id . "' ";
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['medicine'];
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty= $total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock);
}
}
function resetDirectMedicineDispensaryItemsStock($issue_id, $ohc_location_id)
{
$query_for_treatment = " select issue_qty,item_id from direct_medicine_issue_log_details where issue_log_id='" . $issue_id . "' ";
error_log("cdklfjckld" . $query_for_treatment);
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issue_qty'];
$item_id = $row_for_treatemnt['item_id'];
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty= $total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
error_log("fdkhfvkjnfk" . $query_for_reset_item_dispensary_stock);
mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock);
}
}
function resetOPDConsumablesDispensaryItemsStock($opd_id, $ohc_location_id)
{
$query_for_treatment = " select issued_qty,medicine from opd_consumables where consume_id='" . $opd_id . "' ";
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['medicine'];
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty= $total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock);
}
}
function getMinExpiaryDateItem($ohc_location_id, $item_id)
{
$sql_min_expiry_date_item = "select min(expiry_date),item_batch_no from item_stock_dispensary where ohc_location_id=$ohc_location_id and item_id=$item_id and (stock_qty>0) ";
// echo $sql_min_expiry_date_item;
$result_min_expiry_date_item = mysqli_query($GLOBALS['conn'], $sql_min_expiry_date_item);
if ($row_min_expiry_date_item = @mysqli_fetch_array($result_min_expiry_date_item)) {
return $row_min_expiry_date_item['item_batch_no'];
}
}
function toCheckDressingItem($item_id)
{
return getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
}
function getStockQtyAtDispensaryLevel($itemId, $ohc_location_id)
{
$current_stock_query_at_dispensary_level = "select stock_qty from item_stock_dispensary where item_id=$itemId and ohc_location_id=$ohc_location_id ";
error_log("checking item stock dispensary:" . $current_stock_query_at_dispensary_level);
$results_stock_query = @mysqli_query($GLOBALS['conn'], $current_stock_query_at_dispensary_level);
if ($row_stocks = @mysqli_fetch_array($results_stock_query)) {
error_log("returned stockqty:" . $row_stocks['stock_qty']);
return $row_stocks['stock_qty'];
}
return null;
}
function getStockQtyAtStoreLevel($itemId, $ohc_location_id,$item_batch_no)
{
$batch_q='';
if($item_batch_no!='' || $item_batch_no!=null){
$batch_q=" and item_batch_no='$item_batch_no'";
}
$current_stock_query_at_Store_level = "select stock_qty from item_stock where item_id=$itemId and ohc_type_id=$ohc_location_id $batch_q";
error_log("checking item stock Store:" . $current_stock_query_at_Store_level);
$results_stock_query = @mysqli_query($GLOBALS['conn'], $current_stock_query_at_Store_level);
if ($row_stocks = @mysqli_fetch_array($results_stock_query)) {
error_log("returned stockqty:" . $row_stocks['stock_qty']);
return $row_stocks['stock_qty'];
}
return null;
}
/*
* function getStockQtyAtDispensaryLevel($itemId,$ohc_location_id){
*
* $query = "select sum(stock_qty) as total_qty from item_stock_dispensary where item_id = '".$itemId."' and ohc_location_id=$ohc_location_id ";
*
* if (!$result = @mysqli_query($GLOBALS['conn'],$query)) {
* exit(mysqli_error($GLOBALS['conn']));
* }
* $data = array();
* if(mysqli_num_rows($result) > 0) {
* while ($row = mysqli_fetch_assoc($result)) {
* $data['current_stock_level'] = $row['total_qty'];
* }
* }
* else
* {
* $data['status'] = 200;
* $data['message'] = "Data not found!";
* }
* return $data;
* }
*/
/* update Dispesary Stock For Dispensary Item Inward */
function updateDispensaryStockForItemInward($ohc_location_id, $item_id, $inward_qty)
{
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
if ($stock_qty != null && $stock_qty != "") {
$total_qty = floatval($stock_qty) + floatval($inward_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty=$total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
} else {
$query_for_reset_item_dispensary_stock = " insert into item_stock_dispensary set stock_qty=$inward_qty ,item_id=$item_id , ohc_location_id=$ohc_location_id ";
// echo $query_for_reset_item_dispensary_stock;
}
error_log("updateDispensaryStockForItemInward:" . $query_for_reset_item_dispensary_stock);
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock)) {
error_log("Error Occurred:" . mysqli_error($GLOBALS['conn']) . "Query:" . $query_for_reset_item_dispensary_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
// echo $batch_no;
}
function resetDispensaryItemStockForItemInward($ohc_location_id, $received_id)
{
$query_for_item_inward = " select * from received_issue_items where received_id='" . $received_id . "' ";
error_log("resetDispensaryItemStockForItemInward:" . $query_for_item_inward);
$result_for_for_item_inward = mysqli_query($GLOBALS['conn'], $query_for_item_inward);
while ($row_for_item_inward = mysqli_fetch_array($result_for_for_item_inward)) {
$is_packaging_item = $row_for_item_inward['$is_packaging_item'];
if ($is_packaging_item == 'Y') {
updateDispensaryStockForItemInward($ohc_location_id, $row_for_item_inward['dispensary_item'], -$row_for_item_inward['dis_item_qty']);
} else {
updateDispensaryStockForItemInward($ohc_location_id, $row_for_item_inward['item_id'], -$row_for_item_inward['received_qty']);
}
}
}
function resetStockAtStoreLevelForStore($issue_id)
{
$sql_for_issue_items = " select * from stock_issue_items where stock_issue_id=$issue_id ";
$results_issue_items = mysqli_query($GLOBALS['conn'], $sql_for_issue_items);
while ($row_issue_items = mysqli_fetch_assoc($results_issue_items)) {
updateStockAtStoreLevel($row_issue_items['item_id'], $row_issue_items['item_batch_no'], -$row_issue_items['issue_qty']);
error_log("row_issue_items['item_id']" . $row_issue_items['item_id']);
error_log("row_issue_items['item_batch_no']" . $row_issue_items['item_batch_no']);
error_log("row_issue_items['issue_qty']" . $row_issue_items['issue_qty']);
}
}
function getStoreItemExpiryDate($item_id, $batch_no)
{
$sql_min_expiry_date_item = "select expiry_date from item_stock where item_id='" . $item_id . "' and item_batch_no='" . $batch_no . "' ";
// echo $sql_min_expiry_date_item;
$result_min_expiry_date_item = mysqli_query($GLOBALS['conn'], $sql_min_expiry_date_item);
if ($row_min_expiry_date_item = @mysqli_fetch_array($result_min_expiry_date_item)) {
return $row_min_expiry_date_item['expiry_date'];
}
}
function updateStockAtStoreLevel($item_id, $batch_no, $qty)
{
// $query_for_stock_update="update "
$ohc_type_id=$_SESSION['current_ohcttype'];
$current_stock__query = "select stock_qty from item_stock where item_id=$item_id and item_batch_no='" . $batch_no . "' and ohc_type_id='$ohc_type_id' ";
// echo $current_stock__query;
error_log("stock updating vs:" . $current_stock__query . " " . $item_id . " " . $qty);
if (!$results_stock__query = mysqli_query($GLOBALS['conn'], $current_stock__query)) {
error_log("Error Occurred:" . mysqli_error($GLOBALS['conn']) . "Query:" . $current_stock__query);
} else {
$row_stocks = mysqli_fetch_row($results_stock__query);
$current_stock = $row_stocks[0];
$update_qty = floatval($current_stock) - floatval($qty);
$query_for_stock_update = "update item_stock set stock_qty=$update_qty where item_id=$item_id and item_batch_no='" . $batch_no . "' and ohc_type_id='$ohc_type_id' ";
// echo $query_for_stock_update;
if (!mysqli_query($GLOBALS['conn'], $query_for_stock_update)) {
error_log("Error Occurred:" . mysqli_error($GLOBALS['conn']) . "Query:" . $query_for_stock_update);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
function begin()
{
mysqli_query($GLOBALS['conn'], "BEGIN");
}
function commit()
{
mysqli_query($GLOBALS['conn'], "COMMIT");
}
function rollback()
{
mysqli_query($GLOBALS['conn'], "ROLLBACK");
}
function getRecentPatients()
{
/*
*
* $sql_division = "select count(appointment_id) count FROM (select curdate() as day
*
* union select curdate() - interval 1 day
*
* union select curdate() - interval 2 day
*
* union select curdate() - interval 3 day
*
* union select curdate() - interval 4 day
*
* union select curdate() - interval 5 day
*
* union select curdate() - interval 6 day
*
* ) days
*
* left join employee_appointment
*
* on days.day = Date(employee_appointment.appointment_date)
*
* group by
*
* days.day";
*
*/
$sql_division = "select count(appointment_id) count FROM (select curdate() as day
union select curdate() - interval 1 day
union select curdate() - interval 2 day
union select curdate() - interval 3 day
) days
left join employee_appointment
on days.day = Date(employee_appointment.appointment_date)
group by
days.day";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
$data = array();
while ($row_name = @mysqli_fetch_array($result_name)) {
$data[] = $row_name['count'];
}
return implode(', ', $data);
}
function getTodayStockCount()
{
$sql_division = "select sum(current_stock_level) count from tbl_items";
$result_name = mysqli_query($GLOBALS['conn'], $sql_division);
if ($row_name = @mysqli_fetch_array($result_name)) {
return $row_name['count'];
}
}
function validateRequest()
{
$special_chars = "'^�$%()}{~><>,|=_+�-"; // all the special characters you want to check for
$return = array();
$return['msg'] = "Internal Server Error";
foreach ($_REQUEST as $formKey => $formKeyVal) {
if (preg_match('/' . $special_chars . '/', $formKeyVal) || preg_match('/' . $special_chars . '/', $formKey)) {
// echo "<script>alert('Manoj'+'".$formKey."=".$formKeyVal."')</script>";
if (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest') {
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
die(json_encode($return));
}
echo "<script>location.href='error.php'</script>";
}
}
}
/**
*
* Takes input as array suitable for multiselect form attributes
*
* Removes the duplicates and returns a string.
*
*
*
* @param
* Returns a clear comma seperated value string
*
* @return string
*
*/
function getMultiValuedSelectData($attr)
{
$attr = implode(',', $attr);
$explodedArray = array_unique(explode(",", $attr));
if (($key = array_search('', $explodedArray)) !== false) {
unset($explodedArray[$key]);
}
$attr = implode(',', $explodedArray);
return $attr;
}
/**
*
* The method takes input an array of text values for master tables
*
* Searches those already existing and adds the new entries and returns the ids for all master entries.
*
*
*
* @param
* Name of table $tableName
*
* @param
* Name of the column for which values are being passed as input $fieldName
*
* @param
* Name of the key column for the table $keyFieldName
*
* @param
* Array of colum for the values being sent as input $userEntriesArray
*
* @return
*
*/
function handleDynamicMasterInsert($tableName, $fieldName, $keyFieldName, $userEntriesCommaSeperated, $needIds = "0")
{
$userEntriesArray = explode(",", $userEntriesCommaSeperated);
$userEntriesArray = array_map('trim', $userEntriesArray);
error_log("user entries array" . $userEntriesArray);
$sql_existing_entries = "SELECT " . $fieldName . " as field_value from " . $tableName . " where " . $fieldName . " in('" . join("','", $userEntriesArray) . "')";
error_log("existing entries query:" . $sql_existing_entries);
if (!$result_existing_entries = @mysqli_query($GLOBALS['conn'], $sql_existing_entries)) {
error_log("error retrieving:" . mysqli_error($GLOBALS['conn']) . " sql_existing_entries:" . $sql_existing_entries);
}
$existingEntries = array();
error_log("NO. OF COMPLAINTS::" .mysqli_num_rows($result_existing_entries));
if (mysqli_num_rows($result_existing_entries) > 0) {
while ($row = mysqli_fetch_assoc($result_existing_entries)) {
$existingEntries[] = $row['field_value'];
}
}
error_log("Existing Entries:" . $existingEntries);
$newEntries = array_diff($userEntriesArray, $existingEntries);
error_log("New Entries Count:" . sizeOf($newEntries));
if (sizeof($newEntries) > 0) {
for ($i = 0; $i < sizeOf($userEntriesArray); $i++) {
if ($newEntries[$i] != null && $newEntries[$i] != '') {
error_log("NEW ENTRY::" .$newEntries[$i]);
$sql = "insert into " . $tableName . "(" . $fieldName . ",modified_by) values('" . strtoupper(trim($newEntries[$i])) . "'," . $_SESSION['user_id'] . ")";
error_log("INSERTION QUERY::" .$sql);
if (!$result = @mysqli_query($GLOBALS['conn'], $sql)) {
error_log("fail insert query:" . mysqli_error($GLOBALS['conn']));
error_log("failed query:" . $sql);
exit(mysqli_error($GLOBALS['conn']));
}
}
}
}
//if ($needIds != '0') {
// Now return the list of all IDs for both new and old ones to calling system for reference.
$userEntriesArray = array_map('trim', $userEntriesArray);
$query = "select " . $keyFieldName . " as key_id from " . $tableName . " where " . $fieldName . " in('" . join("','", $userEntriesArray) . "')";
error_log(" extraction query:" . $query);
if (!$result = @mysqli_query($GLOBALS['conn'], $query)) {
error_log("error retrieving udpated:" . mysqli_error($GLOBALS['conn']) . " query:" . $query);
//exit ( mysqli_error($GLOBALS['conn']) );
}
$data = array();
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$data[] = $row['key_id'];
}
}
error_log("return data" . $data);
return $data;
//}
}
function getItemWithFormName($itemid)
{
$item_result = mysqli_query($GLOBALS['conn'], "SELECT item_id,trim(concat(ifnull(form_name,''),' ',item_name)) item_name FROM tbl_items i left join medicine_form f on i.item_form_id=f.form_id where i.item_id=$itemid");
if ($item_result) {
if ($row_item = @mysqli_fetch_array($item_result)) {
@extract($row_item);
return $row_item['item_name'];
}
}
}
function getGroupItemsList()
{
$resultArry = array();
$item_result = mysqli_query($GLOBALS['conn'], "SELECT item_id from tbl_items where is_group_item='P'");
if ($item_result) {
while ($row_item = @mysqli_fetch_array($item_result)) {
@extract($row_item);
$resultArry[] = $row_item['item_id'];
}
}
return $resultArry;
}
function getKeyValueMap($tableName, $keyColumn, $valueColumn)
{
$resultArry = array();
$keys = array();
$values = array();
$item_result = mysqli_query($GLOBALS['conn'], "SELECT $keyColumn, $valueColumn from $tableName ");
if ($item_result) {
while ($row_item = @mysqli_fetch_array($item_result)) {
@extract($row_item);
$keys[] = $row_item[$keyColumn];
$values[] = $row_item[$valueColumn];
}
}
$resultArry = array_combine($keys, $values);
return $resultArry;
}
function getKeyValueMapWithWhere($tableName, $keyColumn, $valueColumn, $where_col, $where_value)
{
$resultArry = array();
$keys = array();
$values = array();
$item_result = mysqli_query($GLOBALS['conn'], "SELECT $keyColumn, $valueColumn from $tableName where $where_col='" . $where_value . "' ");
if ($item_result) {
while ($row_item = @mysqli_fetch_array($item_result)) {
@extract($row_item);
$keys[] = $row_item[$keyColumn];
$values[] = $row_item[$valueColumn];
}
}
$resultArry = array_combine($keys, $values);
return $resultArry;
}
function creatingTicketNoOPD()
{
$month = date('n');
$year = date('Y');
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$ohc_type = $_SESSION['current_ohcttype'];
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('-',ticket_no)+1,length(ticket_no)-(5+locate('-',ticket_no))) as UNSIGNED) ) from employee_appointment where ticket_no like '%$year' and appointment_type='O' ";
// echo $query_req_ref_no;
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
$row_ticket_no = mysqli_fetch_row($result_ticket_no);
return $ticket_no = 'OPD' . '-' . ($row_ticket_no[0] + 1) . '-' . $year;
}
function creatingTicketNoFollowup()
{
$month = date('n');
$year = date('Y');
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$ohc_type = $_SESSION['current_ohcttype'];
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('-',ticket_no)+1,length(ticket_no)-(5+locate('-',ticket_no))) as UNSIGNED) ) from followup_details where ticket_no like '%$year' and appointment_type='O' ";
// echo $query_req_ref_no;
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
$row_ticket_no = mysqli_fetch_row($result_ticket_no);
return $ticket_no = 'FLU' . '-' . ($row_ticket_no[0] + 1) . '-' . $year;
}
function creatingTicketNoInjury()
{
$month = date('n');
$year = date('Y');
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$ohc_type = $_SESSION['current_ohcttype'];
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('-',ticket_no)+1,length(ticket_no)-(5+locate('-',ticket_no))) as UNSIGNED) ) from employee_appointment where ticket_no like '%$year' and appointment_type='I' ";
// echo $query_req_ref_no;
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
$row_ticket_no = mysqli_fetch_row($result_ticket_no);
return $ticket_no = 'IR' . '-' . ($row_ticket_no[0] + 1) . '-' . $year;
}
function creatingTicketNofitness()
{
$month = date('n');
$year = date('Y');
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$ohc_type = $_SESSION['current_ohcttype'];
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('-',ticket_no)+1,length(ticket_no)-(5+locate('-',ticket_no))) as UNSIGNED) ) from sickness where ticket_no like '%$year' and ohc_type_id=$ohc_type ";
error_log($query_ticket_no);
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
$row_ticket_no = mysqli_fetch_row($result_ticket_no);
return $ticket_no = 'FITNO' . '-' . ($row_ticket_no[0] + 1) . '-' . $year;
}
function creatingPEMENo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_peme_no = "select task, max( CAST(substring(peme_no,locate('PEME/',peme_no)+5,(locate('/$year',peme_no))) as UNSIGNED) ) from medical_examination where peme_no like '%$fy' and ohc_location_id = '$ohc'";
error_log("PEME::" . $query_peme_no);
// echo $query_req_ref_no;
$result_peme_no = @mysqli_query($GLOBALS['conn'], $query_peme_no);
$row_peme_no = mysqli_fetch_array($result_peme_no);
$peme_no = 'TML/PEME/' . ($row_peme_no[1] + 1) . '/' . $year . '-' . $year1;
error_log("PEME NO." . $peme_no);
return $peme_no;
}
function creatingCheckupTicketNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('TCKT/',ticket_no)+5,(locate('/$year',ticket_no))) as UNSIGNED) ) from checkup_form where ticket_no like '%$fy' and ohc_type_id = '$ohc' ";
error_log("tickt generate query " . $query_ticket_no);
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
$row_ticket_no = mysqli_fetch_array($result_ticket_no);
$ticket_no = 'TVS/TCKT/' . ($row_ticket_no[0] + 1) . '/' . $year . '-' . $year1;
error_log("medical ticket NO." . $ticket_no);
return $ticket_no;
}
function generateOption2($tableName, $nameCol, $idCol, $selectedId, $dis)
{
$optionValue='';
$sql = "select $nameCol,$idCol from $tableName";
error_log("query::::::".$sql);
$resultSet = @mysqli_query($GLOBALS['conn'], $sql );
while ( $row = mysqli_fetch_array ( $resultSet ) ) {
if ($row [$idCol] == $selectedId){
$optionValue .= "<option value='" . $row [$idCol] . "' selected=selected>" . $row [$nameCol] . "</option>";
} else if ($dis == 'div' && $row [$idCol] == '1') {
$optionValue .= "<option value='" . $row [$idCol] . "' disabled >" . $row [$nameCol] . "</option>";
} else{
$optionValue .= "<option value='" . $row [$idCol] . "'>" . $row [$nameCol] . "</option>";
}
}
error_log("option value ".$optionValue);
return $optionValue;
}
function getsearchOptions($roleId)
{
$optionValue='';
$sql = "select distinct(m.menu_id),m.menu_name,m.menu_description,m.menu_url,m.parent_id,m.icon_text from assign_menu a, menu_master m where a.menu_id= m.menu_id and
a.role_id='" . $roleId . "' and (m.menu_url!='' or m.menu_url!=null or m.menu_url!='NA') order by disp_seq";
error_log("query::::::".$sql);
$resultSet = @mysqli_query($GLOBALS['conn'], $sql );
while ( $row = mysqli_fetch_array ( $resultSet ) ) {
$optionValue .= "<option value='" . $row ["menu_url"] . "' >" . $row ["menu_name"] . "</option>";
}
error_log("option value ".$optionValue);
return $optionValue;
}
function creatingPOHCNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_pohc_no = "select task, max( CAST(substring(pohc_no,locate('POHC/',pohc_no)+5,(locate('/$year',pohc_no))) as UNSIGNED) ) from medical_examination where pohc_no like '%$fy' and ohc_location_id = '$ohc'";
error_log("pohc" . $query_pohc_no);
// echo $query_req_ref_no;
$result_pohc_no = @mysqli_query($GLOBALS['conn'], $query_pohc_no);
$row_pohc_no = mysqli_fetch_array($result_pohc_no);
$pohc_no = 'TML/POHC/' . ($row_pohc_no[1] + 1) . '/' . $year . '-' . $year1;
error_log("immmmmmmmme" . $pohc_no);
return $pohc_no;
}
function creatingCylinderNo()
{
$query_cylinder_no = "select max(cast(substring(oxygen_kit_no,17,100) as unsigned)) from oxygen_cylinder where oxygen_kit_no like '%KIT/%'";
error_log("cylinder" . $query_cylinder_no);
// echo $query_req_ref_no;
$result_cylinder_no = @mysqli_query($GLOBALS['conn'], $query_cylinder_no);
$row_cylinder_no = mysqli_fetch_array($result_cylinder_no);
$cylinder_no = 'TML/MED/OXY/KIT/' . ($row_cylinder_no[0] + 1);
error_log("CYLINDER NO." . $cylinder_no);
return $cylinder_no;
}
function creatingCOVNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_cov_no = "select task, max( CAST(substring(cov_no,locate('COV/',cov_no)+4,(4+locate('/$year',cov_no))) as UNSIGNED) ) from medical_examination where cov_no like '%$fy' and ohc_location_id = '$ohc'";
error_log("COV" . $query_cov_no);
// echo $query_req_ref_no;
$result_cov_no = @mysqli_query($GLOBALS['conn'], $query_cov_no);
$row_cov_no = mysqli_fetch_array($result_cov_no);
$cov_no = 'TML/COV/' . ($row_cov_no[1] + 1) . '/' . $year . '-' . $year1;
error_log("COV NO" . $cov_no);
return $cov_no;
}
function creatingWAHNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_wah_no = "select task, max( CAST(substring(wah_no,locate('WAH/',wah_no)+4,(4+locate('/$year',wah_no))) as UNSIGNED) ) from medical_examination where wah_no like '%$fy' and ohc_location_id = '$ohc'";
error_log("WAH" . $query_wah_no);
// echo $query_req_ref_no;
$result_wah_no = @mysqli_query($GLOBALS['conn'], $query_wah_no);
$row_wah_no = mysqli_fetch_array($result_wah_no);
$wah_no = 'FIA/WAH/' . ($row_wah_no[1] + 1) . '/' . $year . '-' . $year1;
error_log("WAH NO." . $wah_no);
return $wah_no;
}
function creatingFFINo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_ffi_no = "select max( CAST(substring(medical_examination_no,locate('FFI/',medical_examination_no)+4,(4+locate('/$year',medical_examination_no))) as UNSIGNED) ) as max from questionaire_master_response where medical_examination_no like '%$fy' and ohc_type_id = '$ohc'";
error_log("FFI generate" . $query_ffi_no);
// echo $query_req_ref_no;
$result_ffi_no = @mysqli_query($GLOBALS['conn'], $query_ffi_no);
$row_ffi_no = mysqli_fetch_array($result_ffi_no);
$ffi_no = 'FIA/FFI/' . ($row_ffi_no['max'] + 1) . '/' . $year . '-' . $year1;
error_log("FFI NO." . $ffi_no);
return $ffi_no;
}
function creatingDLFNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_dlf_no = "select max( CAST(substring(medical_examination_no,locate('dlf/',medical_examination_no)+4,(4+locate('/$year',medical_examination_no))) as UNSIGNED) ) as max from questionaire_master_response where medical_examination_no like '%$fy' and ohc_type_id = '$ohc'";
error_log("dlf generate **************" . $query_dlf_no);
// echo $query_req_ref_no;
$result_dlf_no = @mysqli_query($GLOBALS['conn'], $query_dlf_no);
$row_dlf_no = mysqli_fetch_array($result_dlf_no);
error_log("dlf row_dlf_no". " ----------------- ". $row_dlf_no);
$dlf_no = 'DLFF/DLF/' . ($row_dlf_no['max'] + 1) . '/' . $year . '-' . $year1;
error_log("DLF NO." . $dlf_no);
return $dlf_no;
}
function creatingCHSNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_chs_no = "select max( CAST(substring(medical_examination_no,locate('chs/',medical_examination_no)+4,(4+locate('/$year',medical_examination_no))) as UNSIGNED) ) as max from questionaire_master_response where medical_examination_no like '%$fy' and ohc_type_id = '$ohc'";
error_log("CHS generate **************" . $query_chs_no);
// echo $query_req_ref_no;
$result_chs_no = @mysqli_query($GLOBALS['conn'], $query_chs_no);
$row_chs_no = mysqli_fetch_array($result_chs_no);
error_log("CHS row_chs_no". " ----------------- ". $row_chs_no);
$chs_no = 'CHSF/CHS/' . ($row_chs_no['max'] + 1) . '/' . $year . '-' . $year1;
error_log("CHS NO." . $chs_no);
return $chs_no;
}
function creatinggrnNo()
{
$month=date('n');
$year = date ( 'Y' );
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$query_indent_ref_no = "select max( CAST(substring(item_grn_no,locate('-',item_grn_no)+1,length(item_grn_no)-(5+locate('-',item_grn_no))) as UNSIGNED) ) from grn_master where item_grn_no like '%$year%'";
$result_indent_ref_no = @mysqli_query($GLOBALS['conn'],$query_indent_ref_no );
$row_indent_ref_no = mysqli_fetch_row ( $result_indent_ref_no );
$ref_no = 'GRN-' . ($row_indent_ref_no [0] + 1) . '-' . $year;
error_log("GRN NO." . $ref_no);
return $ref_no;
}
function creatingPoNo()
{
$month=date('n');
$year = date ( 'Y' );
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$query_indent_ref_no = "select max( CAST(substring(item_po_no,locate('-',item_po_no)+1,length(item_po_no)-(5+locate('-',item_po_no))) as UNSIGNED) ) from indent_items where item_po_no like '%$year%'";
$result_indent_ref_no = @mysqli_query($GLOBALS['conn'],$query_indent_ref_no );
$row_indent_ref_no = mysqli_fetch_row ( $result_indent_ref_no );
$ref_no = 'PO-' . ($row_indent_ref_no [0] + 1) . '-' . $year;
error_log("PO NO." . $ref_no);
return $ref_no;
}
function creatingKHSNo()
{
$month = date('n');
$year = date('Y');
$year1 = $year + 1;
$ohc = $_SESSION['current_ohcttype'];
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
$year1 = date('Y');
}
$fy = $year . '-' . $year1;
$query_khs_no = "select max( CAST(substring(medical_examination_no,locate('khs/',medical_examination_no)+4,(4+locate('/$year',medical_examination_no))) as UNSIGNED) ) as max from questionaire_master_response where medical_examination_no like '%$fy' and ohc_type_id = '$ohc'";
error_log("khs generate **************" . $query_khs_no);
// echo $query_req_ref_no;
$result_khs_no = @mysqli_query($GLOBALS['conn'], $query_khs_no);
$row_khs_no = mysqli_fetch_array($result_khs_no);
error_log("khs row_khs_no". " ----------------- ". $row_khs_no);
$khs_no = 'KHSF/khs/' . ($row_khs_no['max'] + 1) . '/' . $year . '-' . $year1;
error_log("khs NO." . $khs_no);
return $khs_no;
}
function getVisitorTicketNo()
{
$month = date('n');
$year = date('Y');
if ($month > 3) {
$year = $year;
} else {
$year = $year - 1;
}
$ohc_type = $_SESSION['current_ohcttype'];
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
$query_ticket_no = "select max( CAST(substring(ticket_no,locate('-',ticket_no)+1,length(ticket_no)-(5+locate('-',ticket_no))) as UNSIGNED) ) from visitors_employee_appointment where ticket_no like '%$year' and ohc_type_id=$ohc_type ";
// echo $query_ticket_no;
$result_ticket_no = @mysqli_query($GLOBALS['conn'], $query_ticket_no);
if ($row_ticket_no = mysqli_fetch_row($result_ticket_no));
return $ticket_no = 'TCKT' . $ohc_type_code . '-' . ($row_ticket_no[0] + 1) . '-' . $year;
}
function getAilmentNamesFromIds($ailment_names_ids)
{
$ailment_names_ids = $rows_emp_appoint['ailments_new'];
$ailment_names_ids_array = array();
$ailment_names_ids_array = explode(",", $ailment_names_ids);
$ailment_names = "";
for ($i = 0; $i < count($ailment_names_ids_array); $i++) {
if ($i == 0) {
$ailment_names = getTableFieldValue('ailment', 'ailment_name', 'ailment_id', $ailment_names_ids_array[$i]);
} else {
$ailment_names = $ailment_names . "," . getTableFieldValue('ailment', 'ailment_name', 'ailment_id', $ailment_names_ids_array[$i]);
}
}
return $ailment_names;
}
function getAilmentSystemNamesFromIds($ailment_system_ids)
{
$ailment_system_ids_array = array();
$ailment_system_ids_array = explode(",", $ailment_system_ids);
$ailment_systems = "";
for ($i = 0; $i < count($ailment_system_ids_array); $i++) {
if ($i == 0) {
$ailment_systems = getTableFieldValue('ailment_system', 'ailment_sys_name', 'ailment_sys_id', $ailment_system_ids_array[$i]);
} else {
$ailment_systems = $ailment_systems . "," . getTableFieldValue('ailment_system', 'ailment_sys_name', 'ailment_sys_id', $ailment_system_ids_array[$i]);
}
}
return $ailment_systems;
}
function getTotalEmployeeBySurveyAssignId($survey_assign_id)
{
$sql_for_emp_survey = "select count(emp_survey_id) count from emp_survey where survey_assign_id=$survey_assign_id";
$result_for_emp_survey = mysqli_query($GLOBALS['conn'], $sql_for_emp_survey);
if ($row_for_emp_survey = @mysqli_fetch_array($result_for_emp_survey)) {
if ($row_for_emp_survey['count'] != null && $row_for_emp_survey['count'] != '')
return $row_for_emp_survey['count'];
else
return 0;
}
}
function getAttemptEmployeeBySurveyAssignId($survey_assign_id)
{
$sql_for_attempt_emp_survey = "select count(distinct(emp_id)) count from emp_survey_answer where survey_assign_id=$survey_assign_id";
$result_for_attempt_emp_survey = mysqli_query($GLOBALS['conn'], $sql_for_attempt_emp_survey);
if ($row_for_attempt_emp_survey = @mysqli_fetch_array($result_for_attempt_emp_survey)) {
if ($row_for_attempt_emp_survey['count'] != null && $row_for_attempt_emp_survey['count'] != '')
return $row_for_attempt_emp_survey['count'];
else
return 0;
}
}
function generateOptionForSingleColumn($tableName, $nameCol, $idCol, $selectedId)
{
echo $sql = "select $nameCol,$idCol from $tableName order by $idCol ";
$resultSet = mysqli_query($GLOBALS['conn'], $sql);
while ($row = mysqli_fetch_array($resultSet)) {
if ($row[$idCol] == $selectedId)
$optionValue .= "<option value='" . $row[$idCol] . "' selected=selected>" . $row[$idCol] . "</option>";
else if ($dis == 'div' && $row[$idCol] == '1') {
$optionValue .= "<option value='" . $row[$idCol] . "' disabled >" . $row[$idCol] . "</option>";
} else
$optionValue .= "<option value='" . $row[$idCol] . "'>" . $row[$idCol] . "</option>";
}
return $optionValue;
}
function getCalculatedDOBFromAge($age)
{
$current_year = date("Y");
$birthYear = $current_year - $age;
$time = strtotime('01/01/' . $birthYear);
$birthDate = date('d-m-Y', $time);
return $birthDate;
}
function getCalculatedBMIFromweight($weight,$height)
{
$w = $weight;
$h= $height/100;
$BMI = $w/($h*$h);
return $BMI;
}
function ageCalculator($dob)
{
if (!empty($dob)) {
$birthdate = new DateTime($dob);
$today = new DateTime('today');
$age = $birthdate->diff($today)->y;
return $age;
} else {
return 0;
}
}
function runSqlGenericSingleRow($sql)
{
error_log("runSqlGenericSingleRow:" . $sql);
$item_result = mysqli_query($GLOBALS['conn'], $sql);
if ($row_item = @mysqli_fetch_array($item_result)) {
@extract($row_item);
return $row_item;
}
return $null;
}
function runSqlGenericFullResult($sql)
{
$item_result = mysqli_query($GLOBALS['conn'], $sql);
return $row_item;
}
function getHoursLapsed($eventTime)
{
$totaldelay = time() - strtotime($eventTime);
if ($totaldelay <= 0) {
return 0;
} else {
$hours = floor($totaldelay / 3600);
}
return $hours;
}
function getElapsedTime($eventTime)
{
$totaldelay = time() - strtotime($eventTime);
if ($totaldelay <= 0) {
return '';
} else {
if ($days = floor($totaldelay / 86400)) {
$totaldelay = $totaldelay % 86400;
return $days . ' days ago.';
}
if ($hours = floor($totaldelay / 3600)) {
$totaldelay = $totaldelay % 3600;
return $hours . ' hours ago.';
}
if ($minutes = floor($totaldelay / 60)) {
$totaldelay = $totaldelay % 60;
return $minutes . ' minutes ago.';
}
if ($seconds = floor($totaldelay / 1)) {
$totaldelay = $totaldelay % 1;
return $seconds . ' seconds ago.';
}
}
}
function getDispensaryDailyItemBalance($ohc_id, $item_id, $date_of_enquiry)
{
$sqlb = "select balance from stock_balance_dispensary where item_id = '$item_id' and ohc_location_id = '$ohc_id' and enquiry_date = '$date_of_enquiry' ";
$resultb = mysqli_query($GLOBALS['conn'], $sqlb);
error_log("enquiry_date" . $sqlb);
return $b;
}
function setDispensaryDailyItemBalance($ohc_id, $item_id, $date_of_enquiry, $item_balance)
{
$sqlb = "insert into stock_balance_dispensary set item_id = '$id',ohc_location_id = '',balance='$b',modified_by='" . $_SESSION['user_id'] . "',enquiry_date = '$d' ";
$resultb = mysqli_query($GLOBALS['conn'], $sqlb);
error_log("gkjhgjfnnnnnn" . $sqlb);
return $b;
}
function getDailyDispensaryItemConsumption($ohc_id, $item_id, $date_of_enquiry)
{
$sql_consume = "select sum(issued_qty) as issued_total from treatment where item_id = '$item_id' and appointment_id in (select appointment_id employee_appointment where ohc_location_id = '$ohc_id' and date_format(date(appointment_date),'%d-%m-%Y') = '$date_of_enquiry' ";
error_log("cccccccons" . $sql_consume);
$result_consume = mysqli_query($GLOBALS['conn'], $sql_consume);
if ($row_consume = mysqli_fetch_array($result_consume)) {
$issued_qty_total = $row_consume['issued_total'];
}
return $issued_qty_total;
}
function updateItemStockDispensaryShiftBalance($ohc_id, $shift_id, $shift_start_date)
{
mysqli_query($GLOBALS['conn'],"BEGIN");
error_log("param ohc_location:" . $ohc_id);
error_log("DATE" . $shift_start_date);
$sql_existing_today_balance = "select * from item_stock_dispensary_shift_balance where record_date = '" . $shift_start_date . "' and ohc_location_id = '$ohc_id' and shift_id=$shift_id ";
error_log("query" . $sql_existing_today_balance);
$num_rows_existing = mysqli_num_rows(mysqli_query($GLOBALS['conn'],$sql_existing_today_balance));
error_log("query" . $num_rows_existing);
if ($num_rows_existing != 0) {
$delete_existing_today_balance = "delete from item_stock_dispensary_shift_balance where record_date = '" . $shift_start_date . "' and ohc_location_id = '$ohc_id' and shift_id=$shift_id ";
error_log("query" . $delete_existing_today_balance);
if (!mysqli_query($GLOBALS['conn'],$delete_existing_today_balance)) {
error_log("Failed to clear existing:" . mysqli_error($GLOBALS['conn']) . " failing query:" . $delete_existing_today_balance);
}
}
if (!$result = mysqli_query($GLOBALS['conn'],"CALL insertShiftData('$shift_id','$ohc_id','$shift_start_date')")) {
error_log("Failed to update Shift Balance:" . mysqli_error($GLOBALS['conn']));
}
// $sql_bulk_insert = "";
// $sql_existing_today_balance = "select * from item_stock_dispensary where ohc_location_id = '$ohc_id' ";
//error_log ( mysql_num_rows ( mysql_query ( $sql_existing_today_balance ) ) );`
//error_log ( "$sql_existing_today_balance" );
// if (! $result_today_bal = mysql_query ( $sql_existing_today_balance )) {
// error_log ( "Failed to get Shift Balance:".mysql_error()." sql_existing_shift_balance:" . $sql_existing_today_balance );
// }
// $sql_bulk_insert .= "insert into item_stock_dispensary_shift_balance(item_id,item_batch_no,ohc_location_id,expiry_date,stock_qty,record_date,shift_id) values";
// while ( $row_bal = @mysql_fetch_array ( $result_today_bal ) ) {
// @extract ( $row_bal );
// $sql_bulk_insert .= "('" . $row_bal ['item_id'] . "','" . $row_bal ['item_batch_no'] . "','" . $row_bal ['ohc_location_id'] . "','" . $row_bal ['expiry_date'] . "','" . $row_bal ['stock_qty'] . "','" . $shift_start_date . "',$shift_id),";
// }
// $sql_bulk_insert=substr($sql_bulk_insert, 0, -1);
// error_log ( "bulk Insert shift Query:" . $sql_bulk_insert );
// $mysqli = new mysqli ( "localhost", "root", "", "mpbirla_live" );
// if ($mysqli->connect_errno) {
// error_log ( "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error );
// }
// if (! $mysqli->multi_query ( $sql_bulk_insert )) {
// error_log( "Multi shift query failed: (" . $mysqli->errno . ") " . $mysqli->error);
// }
// $mysqli->commit ();
mysqli_query($GLOBALS['conn'],"COMMIT");
}
function updateItemStockStoreShiftBalance($shift_id, $shift_start_date)
{
error_log("sdhsdgaeragsdshts:" . $shift_id);
mysqli_query($GLOBALS['conn'], "BEGIN");
error_log("Store Stock shift Balance Record");
$sql_existing_today_balance = "select * from item_stock_store_shift_balance where record_date = '" . $shift_start_date . "' and shift_id=$shift_id ";
error_log("query" . $sql_existing_today_balance);
$num_rows_existing = mysqli_num_rows(mysqli_query($GLOBALS['conn'], $sql_existing_today_balance));
if ($num_rows_existing != 0) {
$delete_existing_today_balance = "delete from item_stock_store_shift_balance where record_date = '" . $shift_start_date . "' and shift_id=$shift_id ";
error_log("query" . $delete_existing_today_balance);
// echo $delete_existing_today_balance;
// if (! mysqli_query($GLOBALS['conn'], $delete_existing_today_balance )) {
// error_log("Failed to clear existing shift records:".mysqli_error($GLOBALS['conn'])." failing query:".$delete_existing_today_balance);
// }
}
$sql_bulk_insert = "";
$sql_existing_today_balance = "select * from item_stock ";
error_log("$sql_existing_today_balance");
if (!$result_today_bal = mysqli_query($GLOBALS['conn'], $sql_existing_today_balance)) {
error_log("sql_existing_shift_balance:" . $sql_existing_today_balance);
}
while ($row_bal = @mysqli_fetch_array($result_today_bal)) {
@extract($row_bal);
$sql_bulk_insert .= "insert into item_stock_store_shift_balance(item_id,item_batch_no,expiry_date,stock_qty,record_date,shift_id) values(";
$sql_bulk_insert .= "'" . $row_bal['item_id'] . "','" . $row_bal['item_batch_no'] . "','" . $row_bal['expiry_date'] . "','" . $row_bal['stock_qty'] . "','" . $shift_start_date . "', $shift_id);";
}
error_log("bulk Insert Query Store Shift:" . $sql_bulk_insert);
$mysqli = new mysqli("localhost", "root", "", "tatametaliks_live");
if ($mysqli->connect_errno) {
error_log("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
}
if (!$mysqli->multi_query($sql_bulk_insert)) {
echo "Multi Shift query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$mysqli->commit();
mysqli_query($GLOBALS['conn'], "COMMIT");
}
function hasChildData($tableName, $nameCol, $selectedId)
{
$sql = " select $nameCol from $tableName where $nameCol='" . $selectedId . "' ";
error_log("**" . $sql);
$results = mysqli_query($GLOBALS['conn'], $sql);
if ($row_consume = mysqli_fetch_array($results)) {
return true;
}
return false;
}
function save_log($requestStr, $feature_name, $feature_action, $file_name)
{
$sql = "insert into audit_log(request_param,feature_name,feature_action,file_name,modified_by) values ('$requestStr','$feature_name','$feature_action','$file_name','" . $_SESSION['user_id'] . "' )";
$results = mysqli_query($GLOBALS['conn'], $sql);
error_log("audit-log:" . $sql);
}
function datediff($date){
date_default_timezone_set('Asia/Kolkata');
$bday = new DateTime($date);
$today = new Datetime(date('d-m-Y'));
$diff = $today->diff($bday);
return $diff->y." Years";
}
function getTodayapproval($type){
$sql = "SELECT count(*) FROM questionaire_master_response where screen_date ='".date('Y-m-d')."' AND form_type ='$type' And approval_status = 'Y' ";
error_log("total".$sql);
$result = mysqli_query($GLOBALS['conn'],$sql);
$row= mysqli_fetch_row($result);
return $row[0];
}
function resetItemStockAtDispensaryLevelForFollowup($appointment_id, $ohc_location_id,$followup_id)
{
$query_for_treatment = " select issued_qty,item_id from treatment where appointment_id='" . $appointment_id . "' and followup_id='".$followup_id."' ";
error_log("query_for_treatment:" . $query_for_treatment);
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['item_id'];
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
if ($is_group_item == 'C') {
resetItemStockAtDispensaryLevelForGroupItems($issued_qty, $item_id, $ohc_location_id);
/*
* $result_for_group_items = getGroupItems($item_id);
*
* while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
*
* $used_qty = $row_for_group_items['qty'];
*
* $item_id = $row_for_group_items['item_id'];
*
* resetItemStockAtDispensaryLevelForGroupItems($used_qty, $item_id, $ohc_location_id);
*
* }
*/
} else if ($is_group_item == 'N') {
// $batch_no = getMinExpiaryDateItem($ohc_location_id, $item_id);
// echo "b".$batch_no;
$stock_qty = getStockQtyAtDispensaryLevel($item_id, $ohc_location_id);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
error_log("stock_qty:" . $stock_qty);
error_log("issued_qty:" . $issued_qty);
error_log("total_qty:" . $total_qty);
$query_for_reset_item_dispensary_stock = " update item_stock_dispensary set stock_qty= $total_qty where item_id=$item_id and ohc_location_id=$ohc_location_id ";
error_log("query_for_reset_item_dispensary_stock:" . $query_for_reset_item_dispensary_stock);
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock)) {
error_log("failed to reset dispensary stock:" . mysqli_error($GLOBALS['conn']) . " Failing Query:" . $query_for_reset_item_dispensary_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
}
function resetItemStockAtStoreLevelForFollowup($appointment_id, $ohc_location_id,$followup_id)
{
$query_for_treatment = " select issued_qty,item_id,item_batch_no from treatment where appointment_id='" . $appointment_id . "' and followup_id='".$followup_id."' ";
error_log("query_for_treatment:" . $query_for_treatment);
$result_for_treatemt = mysqli_query($GLOBALS['conn'], $query_for_treatment);
while ($row_for_treatemnt = mysqli_fetch_array($result_for_treatemt)) {
$issued_qty = $row_for_treatemnt['issued_qty'];
$item_id = $row_for_treatemnt['item_id'];
$item_batch_no = $row_for_treatemnt['item_batch_no'];
$is_group_item = getTableFieldValue('tbl_items', 'is_group_item', 'item_id', $item_id);
if ($is_group_item == 'C') {
resetItemStockAtStoreLevelForGroupItems($issued_qty, $item_id, $ohc_location_id,$item_batch_no);
/*
* $result_for_group_items = getGroupItems($item_id);
*
* while ($row_for_group_items = mysqli_fetch_array($result_for_group_items)) {
*
* $used_qty = $row_for_group_items['qty'];
*
* $item_id = $row_for_group_items['item_id'];
*
* resetItemStockAtDispensaryLevelForGroupItems($used_qty, $item_id, $ohc_location_id);
*
* }
*/
} else if ($is_group_item == 'N') {
// $batch_no = getMinExpiaryDateItem($ohc_location_id, $item_id);
// echo "b".$batch_no;
$stock_qty = getStockQtyAtStoreLevel($item_id, $ohc_location_id,$item_batch_no);
$total_qty = floatval($stock_qty) + floatval($issued_qty);
error_log("stock_qty:" . $stock_qty);
error_log("issued_qty:" . $issued_qty);
error_log("total_qty:" . $total_qty);
$query_for_reset_item_dispensary_stock = " update item_stock set stock_qty= $total_qty where item_id=$item_id and ohc_type_id=$ohc_location_id and item_batch_no='".$item_batch_no."'";
error_log("query_for_reset_item_dispensary_stock:" . $query_for_reset_item_dispensary_stock);
if (!mysqli_query($GLOBALS['conn'], $query_for_reset_item_dispensary_stock)) {
error_log("failed to reset dispensary stock:" . mysqli_error($GLOBALS['conn']) . " Failing Query:" . $query_for_reset_item_dispensary_stock);
rollback();
exit(mysqli_error($GLOBALS['conn']));
}
}
}
}