<?php
	//CLEAR THIS FILE FOR EVERY NEW PROJECT
   include_once("_framework/_nemo.database2.cls.php");

   function getNewEmails()
   {//sidebar menu functions
      return 6;
   }

   function getNewTickets()
   {
      return 1;
   }

    ## GET INFO OR DETAILS FOR EACH FIELD
   function GetTooltip($faqID, $TooltipType)
   {
      global $xdb, $arrSys, $TR, $SP, $HR, $DATABASE_SETTINGS, $SystemSettings;
      ## GET FAQID DETAILS FROM CORRECT LANGUAGE VIEW
      $faqDetails = $xdb->getRowSQL("SELECT * FROM vieFAQ_".$_SESSION[LANGUAGE]." WHERE FAQID = $faqID");

      ## TOOTIP RETURN
      return $faqDetails->txtFAQ;
   }


   function recordBlockMovement($BlockID, $UserID, $intYear, $dtMovement, $strType, $intVines, $txtComments, $txtNotes, $dblHectare=null)
   {
      global $xdb, $arrSys, $TR, $SP, $HR, $DT, $DATABASE_SETTINGS, $SystemSettings;

      //open block record, calculate Ha change
      if($dblHectare === null){
         $rowBlock = $xdb->getRowSQL("SELECT * FROM tblBlock WHERE BlockID = '$BlockID' AND intYear = '$intYear'", 0);
         $dblDensity = $rowBlock->dblVineDensity;
         $dblHectare = round($intVines / $dblDensity,4);
      }

      $bdb = new NemoDatabase("tblBlockMovement", null, null, 0);
      $bdb->Fields[refBlockID] = $BlockID;
      $bdb->Fields[refUserID] = $UserID;
      $bdb->Fields[dtMovement] = $dtMovement;
      $bdb->Fields[strType] = $strType;
      $bdb->Fields[intVines] = $intVines;
      $bdb->Fields[dblHectare] = $dblHectare;
      $bdb->Fields[intYear] = $intYear;
      $bdb->Fields[txtComments] = $txtComments;
      $bdb->Fields[txtNotes] = $txtNotes;
      $bdb->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

      $bdb->Save(0,1);

   }

   function GetNewFarmID($blnTemp=0, $FarmID=null)
   {
      global $xdb, $arrSys, $TR, $SP, $HR, $DT, $DATABASE_SETTINGS, $SystemSettings;


      if($blnTemp == 0)
      {
         $tableRequired = "tblFarm";
      }
      else
      {
         $tableRequired = "tmpFarm";
      }


      if($FarmID == null)
      {
         $NewFarmID = $xdb->getRowSQL("SELECT MAX(FarmID) AS MaxID FROM $tableRequired");
         $NewFarmID = substr(($NewFarmID->MaxID + 1000), 0, -3);

         $NewFarmID = $NewFarmID."000";

      }
      else
      {

         $NewFarmID = substr($FarmID, 0, -3);
         $row = $xdb->getRowSQL("SELECT FarmID FROM $tableRequired WHERE FarmID LIKE '$NewFarmID%' ORDER BY FarmID DESC LIMIT 1");
         $NewFarmID = $row->FarmID+1;
      }


      return $NewFarmID;
   }


   function calculateHectare($dblRowSpacingNarrow, $dblRowSpacingWide, $dblVineSpacing, $intVines)
   {
      $dblHectare = round(($dblRowSpacingNarrow + $dblRowSpacingWide) / 2 * $dblVineSpacing * $intVines / 10000,4);

      return $dblHectare;
   }

   function getBlockDensity($dblHectare, $intVines)
   {
      return round($intVines / $dblHectare, 4);
   }

   function setBlockDensity($BlockID, $intYear, $dblHectare, $intVines)
   {//d = v / ha
      global $xdb, $arrSys, $TR, $SP, $HR, $DATABASE_SETTINGS, $SystemSettings;

      $dblDensity = getBlockDensity($dblHectare, $intVine);
      $xdb->doQuery("UPDATE tblBlock SET dblVineDensity = '$dblDensity' WHERE BlockID = '$BlockID' AND intYear = '$intYear'",1);

      return $dblDensity;
   }


   function CopyBlocks($FarmIDFrom, $FarmIDTo, $arrSelectedBlocks="",$blnTemp=0, $blnNotIn=0)
   {

      global $xdb, $arrSys, $TR, $SP, $HR, $SystemSettings;

      $comma = "";
      $blockIDs = "";
      if($arrSelectedBlocks != "")
      {
         foreach($arrSelectedBlocks AS $ID)
         {
            $blockIDs .= "$comma '$ID'";
            $comma = ",";
         }

         if($blnNotIn == 0)
         {
            $status = "IN";
         }
         else
         {
            $status = "NOT IN";
         }
         $Where = "AND BlockID $status($blockIDs)";

      }

      $xdb->doQuery("   INSERT INTO tmpBlock (  refFarmID, intYear, BlockID, strBlock, strDescription, refSingleVineyardID, refCultivarID, refCultivarRootstockID, refIrrigationID,
                                                   refTrellisID, intYearPlanted, dblHectare, dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, dblVineDensity, strGPS, strStatus,
                                                   intVinesOpening, intVinesUprooted, intVinesGrafted, intVinesAmmended, intVinesClosing, dblHectareUprooted, dblHectareGrafted,
                                                   dblHectareAmmended, intYearUprooted, intYearGrafted, intYearAmmended, txtNotes )
                           SELECT   refFarmID, intYear, BlockID, strBlock, strDescription, refSingleVineyardID, refCultivarID, refCultivarRootstockID, refIrrigationID,
                                    refTrellisID, intYearPlanted, dblHectare, dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, dblVineDensity, strGPS, strStatus,
                                    intVinesOpening, intVinesUprooted, intVinesGrafted, intVinesAmmended, intVinesClosing, dblHectareUprooted, dblHectareGrafted,
                                    dblHectareAmmended, intYearUprooted, intYearGrafted, intYearAmmended, txtNotes
                           FROM tblBlock WHERE refFarmID = $FarmIDFrom $Where");

      // $rst = $xdb->doQuery("SELECT * FROM tblBlock WHERE refFarmID = $FarmIDFrom $Where");
      // while($row = $xdb->fetch_object($rst))
      // {


      //    $xdb->doQuery("INSERT IGNORE INTO tmpBlock
      //                   (refFarmID, intYear, BlockID, strBlock, strDescription, refSingleVineyardID, refCultivarID, refCultivarRootstockID, refIrrigationID, refTrellisID,
      //                   intYearPlanted, dblHectare, dblRowSpacingWide, dblRowSpacingNarrow, dblVineSpacing, dblVineDensity, strGPS, strStatus, intVinesOpening, intVinesUprooted, intVinesGrafted,
      //                   intVinesAmmended, intVinesClosing, dblHectareUprooted, dblHectareGrafted, dblHectareAmmended, intYearUprooted, intYearGrafted, intYearAmmended, txtNotes)
      //          VALUES ( $FarmIDTo,
      //                   '".qs($row->intYear)."',
      //                   '".qs($row->BlockID)."',
      //                   '".qs($row->strBlock)."',
      //                   '".qs($row->strDescription)."',
      //                   '".qs($row->refSingleVineyardID)."',
      //                   '".qs($row->refCultivarID)."',
      //                   '".qs($row->refCultivarRootstockID)."',
      //                   '".qs($row->refIrrigationID)."',
      //                   '".qs($row->refTrellisID)."',
      //                   '".qs($row->intYearPlanted)."',
      //                   '".qs($row->dblHectare)."',
      //                   '".qs($row->dblRowSpacingWide)."',
      //                   '".qs($row->dblRowSpacingNarrow)."',
      //                   '".qs($row->dblVineSpacing)."',
      //                   '".qs($row->dblVineDensity)."',
      //                   '".qs($row->strGPS)."',
      //                   'New',
      //                   '".qs($row->intVinesOpening)."',
      //                   '".qs($row->intVinesUprooted)."',
      //                   '".qs($row->intVinesGrafted)."',
      //                   '".qs($row->intVinesAmmended)."',
      //                   '".qs($row->intVinesClosing)."',
      //                   '".qs($row->dblHectareUprooted)."',
      //                   '".qs($row->dblHectareGrafted)."',
      //                   '".qs($row->dblHectareAmmended)."',
      //                   '".qs($row->intYearUprooted)."',
      //                   '".qs($row->intYearGrafted)."',
      //                   '".qs($row->intYearAmmended)."',
      //                   '".qs($row->txtNotes)."'
      //                   )");
      // }

   }


   //20160121 - get the smallest common denominator of locations - PJ
   function bg1GetSmallestCommonLocation($HarvestID, $MODE="tbl")
   {

      global $xdb, $arrSys, $TR, $SP, $HR, $SystemSettings;

      if($_SESSION[LANGUAGE] == "") $_SESSION[LANGUAGE] = "EN";
      $rst = $xdb->doQuery("SELECT tblFarm.FarmID, tblFarm.strFarm, vieLocation.strLocation, vieLocation.WOCode AS 'LocationCode', vieLocGeo_EN.WOCode AS 'GeoCode', vieLocRegion_EN.WOCode AS 'RegionCode', vieLocDistrict_EN.WOCode AS 'DistrictCode', vieLocWard_EN.WOCode AS 'WardCode', vieLocGeo_EN.strLocation, vieLocRegion_EN.strLocation, vieLocDistrict_EN.strLocation, vieLocWard_EN.strLocation
                              FROM ((((". $MODE ."HarvestItem
                              INNER JOIN (tblFarm
                              INNER JOIN vieLocation ON tblFarm.refLocationID = vieLocation.LocationID) ON ". $MODE ."HarvestItem.refFarmID = tblFarm.FarmID)
                              INNER JOIN vieLocGeo_EN ON LEFT(vieLocation.WOCode, 2) = vieLocGeo_EN.WOCode)
                              INNER JOIN vieLocRegion_EN ON LEFT(vieLocation.WOCode, 5) = vieLocRegion_EN.WOCode)
                              INNER JOIN vieLocDistrict_EN ON LEFT(vieLocation.WOCode, 8) = vieLocDistrict_EN.WOCode)
                              INNER JOIN vieLocWard_EN ON vieLocation.WOCode = vieLocWard_EN.WOCode
                           WHERE refHarvestID=$HarvestID",0); //note: query works in access, but resets joins in design mode [left( ,x)]

      $counter = 0;
      while($row = $xdb->fetch_object($rst))
      {
         $arrGeo[$counter] = $row->GeoCode;
         $arrRegion[$counter] = $row->RegionCode;
         $arrDistrict[$counter] = $row->DistrictCode;
         $arrWard[$counter] = $row->WardCode;

         $counter++;
      }

      $uniqueGeo = count(array_unique($arrGeo));
      $uniqueRegion = count(array_unique($arrRegion));
      $uniqueDistrict = count(array_unique($arrDistrict));
      $uniqueWard = count(array_unique($arrWard));

      if($uniqueWard == 1)
      {
         $Location = $arrWard[0];
         $rowLocation = $xdb->getRowSQL("SELECT * FROM vieLocWard_".$_SESSION[LANGUAGE]." WHERE WOCode = '$Location'",0);
      }
      else
      {
         if($uniqueDistrict == 1)
         {
            $Location = $arrDistrict[0];
            $rowLocation = $xdb->getRowSQL("SELECT * FROM vieLocDistrict_".$_SESSION[LANGUAGE]." WHERE WOCode = '$Location'",0);
         }
         else
         {
            if($uniqueRegion == 1)
            {
               $Location = $arrRegion[0];
               $rowLocation = $xdb->getRowSQL("SELECT * FROM vieLocRegion_".$_SESSION[LANGUAGE]." WHERE WOCode = '$Location'",0);
            }
            else
            {
               if($uniqueGeo == 1)
               {
                  $Location = $arrGeo[0];
                  $rowLocation = $xdb->getRowSQL("SELECT * FROM vieLocGeo_".$_SESSION[LANGUAGE]." WHERE WOCode = '$Location'",0);
               }
            }
         }
      }

      return $rowLocation;
   }

?>