data misplacing within session while i m getting same session id while refreshing

[sarika81286]

i m getting same session id but data of other sessions on my screen while accessing my page
i have recently change 32 bit 2003 server with 64bit win 2008 R2 serv pack1 & i m getting this problem while other pages works fine only one page giving this problem.
we r using tomcat5.0 and j2sdk for devlopment.

any one please help if u hv any idea regarding this,thanks in advance..

[chaarmann]

Are you using a proxy server?
then it may happen that user B gets the same page that user A requested before, because it comes from the cache of the proxy server.
Solution: add a counter number argument to every URL to avoid caching.

A less likely problem: two clients have the same IP-address. In that case the web server can't differentiate the clients and uses the same session for them.

[sarika81286]

Are you using a proxy server?
then it may happen that user B gets the same page that user A requested before, because it comes from the cache of the proxy server.
Solution: add a counter number argument to every URL to avoid caching.

A less likely problem: two clients have the same IP-address. In that case the web server can't differentiate the clients and uses the same session for them.

yes we r using proxy server i have try by added sessionid as well as some randomly generated no with each url but still getting same proplem.

[chaarmann]

Please make sure that you have added the random no to the requesting page and not to the result page!

Example: Page A has a link to call page B.
User 1 is on page A and clicks the link so that B with his data is created.
Then user 2 calls page A and then two things can happen: if the link has the the same URL, the result page comes from the proxy server (and no request goes to your server) and it returns the results from user 1 ! Now User 2 surfs as user 1; he has captured his session. If it is a different URL, then the request bypasses the proxy server and your server generates a new page which is sent back.

So make sure the link is not something like "http://firstPageAfterL ogin.jsp" for both, but "http://firstPageAfterL ogin.jsp?reques tNumber=1" for user A and "http://firstPageAfterL ogin.jsp?reques tNumber=2" for user B

Remark:
random no is ok for now, but it may generate two times the same random no. this is more and more likely the more users you have. So use a systemwide unique counter instead.

[sarika81286]

Please make sure that you have added the random no to the requesting page and not to the result page!

Example: Page A has a link to call page B.
User 1 is on page A and clicks the link so that B with his data is created.
Then user 2 calls page A and then two things can happen: if the link has the the same URL, the result page comes from the proxy server (and no request goes to your server) and it returns the results from user 1 ! Now User 2 surfs as user 1; he has captured his session. If it is a different URL, then the request bypasses the proxy server and your server generates a new page which is sent back.

So make sure the link is not something like "http://firstPageAfterL ogin.jsp" for both, but "http://firstPageAfterL ogin.jsp?reques tNumber=1" for user A and "http://firstPageAfterL ogin.jsp?reques tNumber=2" for user B

Remark:
random no is ok for now, but it may generate two times the same random no. this is more and more likely the more users you have. So use a systemwide unique counter instead.

still same problem is coming frequency is reduced but if i click on link before loading whole page if i refresh it again then i get two peoples data.

[chaarmann]

Then there is no way around anymore: post your code!
(The pages A an B as I wrote in my last e-mail. Server and client code. Leave out all unnecessary stuff that doesn't belong to the problem)

[sarika81286]

Then there is no way around anymore: post your code!
(The pages A an B as I wrote in my last e-mail. Server and client code. Leave out all unnecessary stuff that doesn't belong to the problem)

first file code to call the second file ...

private void checkfordcr(int elevel,Vector text,Vector links) throws Exception
    {
        String dcrlevel = "DCRLEVEL"+elevel;

        //String tm = "?tm=" + String.valueOf(System.currentTimeMillis());
         String ssid = "?session="+sid+"&tm="+ String.valueOf(System.currentTimeMillis()*100000);

        try
        {
                Statement stmt = con.createStatement();

                String sql = "Select SVarName,SVarTxt From Sysprm Where SVarName Like '"+dcrlevel+"%' and SVarTxt <> '' Order by Idsysprm";

                ResultSet rs = stmt.executeQuery(sql);
                String remstr = "";
                java.util.StringTokenizer st = null;

                while(rs.next())
                {
                    st = new java.util.StringTokenizer(rs.getString("SVarTxt"),";");
                    remstr = st.nextToken();
                    links.add("/eis.dataentry." + remstr + ssid);
                    remstr = st.nextToken();
                    text.add(remstr);
                }
                    rs.close();
                    stmt.close();
        }catch(Exception e)
        {
            System.out.println("Exception " + e);
        }
    }
//second file whole coding

package eis.dataentry;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import eis.util.*;
import java.util.*;
/** Creates the DCR enetry form for both field staff as well as administrator can edit DCR for field force.
    For field staff, the DCR date is automatically generated based on the last confirmed DCR entry.
    For Administrator staff, the DCR date is editable.
**/

public class DCREntryScreen extends HttpServlet
{
    static final String pathPrefix = "/"+PropertyHandler.getProperty("ContextName") +"/";
	static final String servPath = pathPrefix + "servlet";

    String pname001 ;
    String pname003 ;
    PrintWriter out ;
    Vector ecode=new Vector();
    String mthname="";
    String wmth="", wyr="";
    String wrkdate="";

    //static String rem = "N";
    public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException, IOException
    {
        doGet(req, res);
    }
	public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
	{
        Connection con = null;
        try
        {
            res.setContentType("text/html");
            out = res.getWriter();
            String dbname,empcode = "";
            HttpSession s;

            synchronized(this)
             {
    		      s = req.getSession(true);
             }

	    	if(s.isNew())
            {
	    	    out.println("<center><h2>You are not authorised to view this page</h2></center>");
             
            }
		    else
            {
               	String wdate = s.getAttribute("WorkingDay").toString()+"/"+s.getAttribute("WorkingMonth").toString()+"/"+s.getAttribute("WorkingYear").toString();
                wmth=s.getAttribute("WorkingMonth").toString();
                wyr=s.getAttribute("WorkingYear").toString();
                wrkdate = ""+s.getAttribute("WorkingYear").toString()+"-"+s.getAttribute("WorkingMonth").toString()+"-"+s.getAttribute("WorkingDay").toString();
                out.println("<html><head>");
                out.println("<title> DCR ENTRY SCREEN </title></head>");
                out.println(Formatter.pageHTMLDesign(pathPrefix));
                out.println("<link id=\"webfx-tab-style-sheet\" type=\"text/css\" rel=\"stylesheet\" href=\""+pathPrefix+"css/tab.webfx.css\">");
                out.println("<script type=\"text/javascript\" src=\""+pathPrefix+"js/webfxlayout.local.js\"></script>");
                out.println("<script type=\"text/javascript\" src=\""+pathPrefix+"js/tabpane.js\"></script>");
                dbname = s.getAttribute("DB").toString();  // database
                String financialyr = s.getAttribute("FinancialStartDate").toString().substring(2,4) + s.getAttribute("FinancialEndDate").toString().substring(2,4);

                 synchronized(this)
                  {
                      con = ConnectionHandler.getConnection(dbname);
                  }

                String valexists1 = getsysprmcount("confirmdcrproduct",con);
                String remarks=remarkExists(dbname,valexists1,"confirmdcrproduct","N");
                if(remarks.equalsIgnoreCase("Y"))
                   out.println("<script type=\"text/javascript\" src=\""+pathPrefix+"js/checkDCREntryNew.js\"></script>");
                else
                   out.println("<script type=\"text/javascript\" src=\""+pathPrefix+"js/checkDCREntry.js\"></script>");

                pname001 = CommonUtils.getHeaderName("PNAME001",dbname);
                pname003 = CommonUtils.getHeaderName("PNAME003",dbname);
                String LogFile = pathPrefix + "debugfile.txt";
                //String tcpDrchk=req.getParameter("tcpDrchk");
                //if(tcpDrchk==null)tcpDrchk="N";
                if(s.getAttribute("FirstTime")==null)
                {
                    s.setAttribute("FirstTime","true");
                }
                else
                {
                    s.setAttribute("FirstTime","false");
                }
                String serial = req.getParameter("serial");
                if(serial==null)
                    serial = "";

                String prefix = req.getParameter("prefix");
                String pob=req.getParameter("pob");
                if(prefix==null)
                    prefix = "";

                String wwith = req.getParameter("wwith");
                if(wwith==null)
                    wwith = "";

                String flag=req.getParameter("flag");
                if(flag==null)
                  flag="";

                String wnetid = req.getParameter("wnetid");
                if(wnetid==null)
                    wnetid = "";

                String temp="";
               // String ename ="";

                String changeupr = req.getParameter("changeupr");
                if(changeupr==null)
                    changeupr = "";

                boolean officeuser = false;
                if(((Integer)s.getAttribute("Elevel")).intValue() > 6)
                    officeuser = true;

                String dcrdate = "";

                String holdcrdate ="";
                String mode = req.getParameter("mode");

                if(mode==null)
                {
                    mode="";
                }
                if(mode.equalsIgnoreCase("holdcr"))
                {
                    holdcrdate = req.getParameter("selholmst");
                    if(holdcrdate!=null)
                      s.setAttribute("FirstTime","true");
                }

                ecode=new Vector();

                if(mode.equalsIgnoreCase("joinwrk"))
                {
                //ecode=new Vector();
                Enumeration e =req.getParameterNames();
                   while(e.hasMoreElements())
                   {      // ecode=new Vector();
                       temp =(String)e.nextElement();
                       Vector tempVector=new Vector();
                       if(temp.substring(0,3).equalsIgnoreCase("chk"))
                       {
                        tempVector = new Vector();
                        tempVector.add(temp.substring(3,temp.indexOf("+")));
                        tempVector.add(temp.substring(temp.indexOf("+")+1,temp.length()));
                        ecode.add(tempVector);
                       }
                    } // while closes
                   // to change tcp of selected netid in case of upper level
                }
                s.setAttribute("EmpVector",ecode);

                if(mode.equalsIgnoreCase("psr"))
                {
                      String wwith2,wwith3,wwith4;
                      String jntwrk=req.getParameter("jntwrk");
                      if(jntwrk==null)
                          jntwrk="";

                      String jw[]=new String[5];
                      for(int i=0;jntwrk.length()>0;i++)
                         {
                             jw[i]=jntwrk.substring(0,jntwrk.indexOf(","));
                             jntwrk=jntwrk.substring(jntwrk.indexOf(",")+1,jntwrk.length());
                          }

                      Vector tmpVector=new Vector();

                      for(int i=0;i<5;i++)
                         {
                            try
                              {
                                 Statement st=con.createStatement();
                                 String query=" select ename from empmst where ecode='"+jw[i]+"' ";
                                 ResultSet rs=st.executeQuery(query);
                                 if(rs.next())
                                   {
                                   tmpVector = new Vector();
                                   tmpVector.add(jw[i]);
                                   tmpVector.add(rs.getString(1));
                                   ecode.add(tmpVector);
                                   }
                               }
                               catch(Exception e)
                               {
                                   Log.log("101 :"+e.toString());
                               }
                         }
                     s.removeAttribute("EmpVector");
                     s.setAttribute("EmpVector",ecode);
                   }

                /*
                  If Employee level is of Administrator Level.
                  then the selected empcode,dcrdate has to pick up from main screen, has to get information
                  about that perticular employee.(viz empcode,netid,dcrdate etc.).
                */

                if(s.getAttribute("FirstTime").toString().equalsIgnoreCase("true"))
                {
                        if(officeuser)
                        {
                            empcode = req.getParameter("dcrecode");
                            dcrdate = req.getParameter("dcrdate");
                        }
                        else
                        {
                            synchronized(this)
                            {
                            empcode = s.getAttribute("EmpCode").toString();
                            }
                        }

                    String[] info = null;
                    InfoHelper ih = null;

                   // synchronized(this) // Added by kiran & glenn to make call of these function synchronized.
                   // {
                        ih = new InfoHelper(con,empcode,dbname);
                        info = ih.getInfo(dcrdate,con);     // Added Parameter con By Glenn & kiran
                    //}

                    /*
                      CHECKS FOR NETWORK TRANSACTION ENTRIES FOR THE PERTICULATAR NETID
                      IN NETTRANS FILE FOR THIS PERTICULAR MONTH AND YEAR OF THE DCR DATE WHICH IS SELECTED
                      IF RECORD EXIST IN FILE, THE ALLOW TO ENTER DCR ENTRY AND WILL SET THE INFORMATION
                      LIKE info[0] as NETID,info[1] as HQ NAME, info[2] as DCRDATE
                      OTHERWISE WILL NOT ALLOW TO ENTER DCR ENTRY SCREEN
                   */
                    if(info[0].equalsIgnoreCase(""))
                    {
                        out.println("\n<br><0br><center><h2><u>No Network transaction entries found</u></h2>");
                        out.println("\n<br>No Network transaction entries were found for this network.");
                        out.println("\n<br>You may not enter your DCR details till the Network Transaction file is updated.");
                        out.println("\n<br><br> <input type=button value=Close onClick=window.close()>");
                    }
                    // if noNetTransEntries closes
                    else if(!info[0].equalsIgnoreCase(info[3]) && !info[3].equalsIgnoreCase(""))
                    {
                        out.println(conflictNetids(info[0],info[1],info[2],info[3],info[4],empcode));
                    }
                    else if((MtpCheck(dbname,s,empcode,info[0])))    //added by dipen...
                    {
                        out.println("<center><br><br><br><br><br><font size=3 face=arial color=red><b>Please Check and Fill the Previous Month MTP Table Correctly</b>");
                        out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
                    }
                     else if(ExpCheck(con))
                    {
                        out.println("<center><br><br><br><br><br><font size=3 face=arial color=blue><b>Expense Processing is going on...<br>Please try after half hour...</font></b>");
                        out.println("<br><br><br><font size=3 face=arial color=red><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></font></center>");
                    }
                    else
                    {
                        PreparedStatement stmt=null;
                        String sql = "SELECT count(*) as SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek' ";
                        stmt = con.prepareStatement(sql);
                        String valueexist="";
                        ResultSet rs = stmt.executeQuery();
                        while(rs.next())
                        {
                          valueexist = rs.getString("SVarTxt");
                        }
                        if(valueexist.equals("0"))
                        {
                             sql="select max(IdSysPrm)+1 as IDSYS from sysprm";
                             stmt = con.prepareStatement(sql);
                             String sysprmid="";
                             rs = stmt.executeQuery();
                             while(rs.next())
                             {
                                sysprmid = rs.getString("IDSYS");
                             }

                             stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values(" +sysprmid+ ",'DCRWeek','0,0')");
                             rs.close();
                        }

                        // Dcr Date Is Stored In info[2] The Logic For Dcr Validation Will Come Here
                        sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";
                        stmt = con.prepareStatement(sql);
                        String weekvalue="";
                        rs = stmt.executeQuery();
                        if(rs.next())
                          weekvalue=rs.getString(1);

                        if(!weekvalue.equalsIgnoreCase("0,0"))
                        {
                           if(checkForWeekDcr(con,empcode,info[0]))
                           {
                             info[2] = dcrWeeklyValidationForElder(info[2],empcode,info[0],s.getAttribute("DB").toString());
                           }
                           else if(checkForDaysDcr(con,empcode,info[0],info[2]))
                           {
                             info[2] = dcrDaysValidationForElder(info[2],empcode,info[0],s.getAttribute("DB").toString());
                           }
                        }
                        rs.close();


                        InfoHelper ih2 = null;
                        String[] infoholdate = null;
                        ih2 = new InfoHelper(con,empcode,dbname);
                        infoholdate = ih2.getInfoholdate(info[2],con);
                        if(!holdcrdate.equalsIgnoreCase(""))
                        {
                        infoholdate[2] = holdcrdate;
                        out.println("<table class=linktable align=center width=100%><tr>" +
                        "<td><b><center><font color=red>NOTE :- To Change The DCR Date Which You Selected Now, Please Login Again.</font></center></b></td></tr></table>");
                        }
                        s.setAttribute("DCRNetID",infoholdate[0]);
                        s.setAttribute("DCRHQName",infoholdate[1]);

                        //Log.log("101 : "+infoholdate[2]);

                        s.setAttribute("DCRDate",infoholdate[2]);
                        s.setAttribute("DCREmp",empcode);
                      //  s.setAttribute("ecode",ecode);
                        s.setAttribute("DCREmpName",ih2.getEmployeeName());
                        s.setAttribute("DCRNetIDLevel",infoholdate[5]);
                        s.setAttribute("DCRNetIDJoinDate",infoholdate[6]);

                    }
                    //getSRFCMTP(dcrdate,s.getAttribute("DCRNetID").toString(),dbname,empcode);
                 }

                //Log.log("786 : "+s.getAttribute("EmpCode").toString());
                Calendar c = Calendar.getInstance();
                String currdate = c.get(c.YEAR)+"-"+(c.get(c.MONTH)+1)+"-"+c.get(c.DATE);
            /*      Enumeration sss=s.getAttributeNames();

            while(sss.hasMoreElements()){
                    String n=(String)sss.nextElement();
                    out.println("<b>"+n+"="+s.getAttribute(n)+"<b><br>");
                }
             */
          //todo sarika
       //************************************************************************************
          Statement stsystp=con.createStatement();
          ResultSet rssystp=stsystp.executeQuery("select svartxt from sysprm where svarname='TPRequire' ");
            //try{
                String systpvar="";
                if(rssystp.next())
                {
                 systpvar=rssystp.getString(1);
                }
                else
                {
                     String sql="select max(IdSysPrm)+1 as IDSYS from sysprm";
                     Statement stmt = con.prepareStatement(sql);
                     String sysprmid="";
                     ResultSet rs = stmt.executeQuery(sql);
                     while(rs.next())
                     {
                          sysprmid = rs.getString("IDSYS");
                     }
                     stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values ( " +sysprmid+ ",'TPRequire','N')");
                     rs.close();

                     stsystp=con.createStatement();
                     rssystp=stsystp.executeQuery("select svartxt from sysprm where svarname='TPRequire' ");
            //try{
                     systpvar="";
                     if(rssystp.next())
                     {
                       systpvar=rssystp.getString(1);
                     }
               }


         //if(!systpvar.equalsIgnoreCase("0"))
         //{
            Log.log("systpvar : "+systpvar);
            String mtpmissed=checkMTPFill(dbname,s,s.getAttribute("DCREmp").toString(),s.getAttribute("DCRNetID").toString(),systpvar);

          if(!mtpmissed.equalsIgnoreCase(""))
          {
              if(!mtpmissed.equalsIgnoreCase(""))
              {
                  if(systpvar.equalsIgnoreCase("N"))
                     mtpmissed=mtpmissed.substring(0,mtpmissed.length()-1);
                  else if(systpvar.equalsIgnoreCase("Y"))
                     mtpmissed=mtpmissed.substring(0,mtpmissed.length());
              }

              if(!mtpmissed.equalsIgnoreCase(""))
              {   //  Log.log(""+145154);
                    String dcrdt = s.getAttribute("DCRDate").toString();
                    String mnth = DateUtils.fillMonthNumber(DateUtils.toDMY(dcrdt).substring((DateUtils.toDMY(dcrdt).indexOf("/"))+1,DateUtils.toDMY(dcrdt).lastIndexOf("/")));
                    if(systpvar.equalsIgnoreCase("N"))
                    {
                        out.println("<center> <br><br><br><br><br><br><br> " +
                        "<font size=3 face=arial color=red>Montly Tour Programme for the dates <font size=5 color=blue><br><u><b>"+mtpmissed+"</U></b> Of The Month: <b><u> "+mnth+"</B></U><br></font> Is Not Entered or Not Approved. </b>");
                        out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
                    }
                    else if(systpvar.equalsIgnoreCase("Y"))
                    {
                        out.println("<center> <br><br><br><br><br><br><br> " +
                        "<font size=3 face=arial color=red>Montly Tour Programme Not Filled For Month <font size=5 color=blue><br><u><b>"+mtpmissed+"</U></b><b><u></B></U><br></font> Is Not Entered or Not Approved . </b>");
                        out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
                    }
                 }
             }
        // }
         //*********************************************************************************
         else if(!(ShdCheck(s.getAttribute("DCRNetID").toString(),s.getAttribute("DCRDate").toString(),dbname).equalsIgnoreCase("")))   //added by dipen
            {
               String sh=ShdCheck(s.getAttribute("DCRNetID").toString(),s.getAttribute("DCRDate").toString(),dbname);
               out.println("<center><br><br><br><br><br><font size=3 face=arial color=red><b><u>"+sh.substring(sh.lastIndexOf("/")+1,sh.length())+"</u>   for   Date: <u>"+s.getAttribute("DCRDate").toString()+"</u>  not Approved </b>");
               out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
              }
         else if(ExpCheck(con))
             {
                out.println("<center><br><br><br><br><br><font size=3 face=arial color=blue><b>Expense Processing is going on...<br>Please try after half hour...</font></b>");
                out.println("<br><br><br><font size=3 face=arial color=red><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></font></center>");
             }
         else if(SampleOpeningCheck(con,s.getAttribute("DCRNetID").toString(),financialyr,s.getAttribute("DCRDate").toString()))
             {
                out.println("<center><br><br><br><br><br><font size=2 face=arial color=blue><b>You can not entered Daily Call Report because You have not entered productwise opening balance... </font></b>");
                out.println("<center><br><font size=2 face=arial color=blue><b>If you want to entered then </font></b>");
                out.println("<A HREF=\"" + servPath + "/eis.dataentry.SampleIssue?choice=opbal&netid=" + s.getAttribute("DCRNetID").toString() + "&emp=" + s.getAttribute("DCREmp").toString() + "\" class=\"hypadd\">CLICK HERE</a>");
                out.println("<br><br><font size=3 face=arial color=red><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></font></center>");
             }
         else if(LeavePendingCheck(con,s.getAttribute("DCRDate").toString(),s.getAttribute("DCREmp").toString()))
         {
             out.println("<table class=linktable align=center width=100%><tr>" +
                         "<td><b><center>DCR ENTRY SCREEN</center></b></td></tr></table>");
             out.println("<center><br><br><br><font size=3 face=arial color=blue><b>You can not entered Daily Call Report because your Leave Approval is pending for this date.....<br>Please contact your Immediate manager...</font></b>");
             out.println("<br><br><br><font size=3 face=arial color=red><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></font></center>");
         }

         //******************************************************************************88
          else
          {
               if(Integer.parseInt(s.getAttribute("Elevel").toString())==1)    //DCRNetIDLevel
               {
                 if(!noTCPEntries(s.getAttribute("DCRNetID").toString(),s.getAttribute("DB").toString()))
                 {
                    if(DateUtils.isAfter(s.getAttribute("DCRDate").toString(),currdate))
                    {
                        out.println("<br><br><br><center><font face=arial color=red size=4><b>ALERT!!!</b></font>");
                        out.println("<br>"+s.getAttribute("DCRDate").toString()+"<br>");
                        out.println("<br><br><center><font face=arial color=red size=4><b>SORRY, YOU CAN NOT ENTER DCR GREATER THAN CURRENT DATE</b></font>");
                        out.println("<br><br><br><input type=button class=buttoncolor value=Close onClick=window.close()>");
                    }
                    else
                    {
                        if(checkdcrdate(con,s.getAttribute("DCREmp").toString()))
                        {
                          String gracedays="";
                          String val = "";
                        //********************************************
                          try
                          {
                            Statement stmt = con.createStatement();

                            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";

                            ResultSet rs = stmt.executeQuery(sql);
                            while(rs.next())
                                 {
                                    val = rs.getString("SVarTxt");
                                 }
                                    con.setAutoCommit(false); //added by sagar
                                    con.commit();//added by sagar
                                    rs.close();

                            StringTokenizer st = new StringTokenizer(val,",");
                             while(st.hasMoreTokens())
                             {
                                 st.nextToken();
                                 gracedays=st.nextToken();
                             }
                           }
                          catch(SQLException e)
                           { }

                        //********************************************
                           if(!val.equalsIgnoreCase("0,0"))
                           {
                            out.println("<table class=linktable align=center width=100%><tr>" +
                                        "<td><b><center>DCR ENTRY SCREEN</center></b></td></tr></table>");

                            out.println("<center> <br><br><br><br><br><br><br> " +
                                        "<font size=3 face=arial color=red> YOUR DCR IS BLOCKED SINCE THE SAME IS NOT FILLED FOR "+gracedays+" OR ");
                            out.println("<br>MORE DAYS PLEASE CONTACT YOUR MANAGER</font> </b>");
                            out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
                           }
                           else
                               out.println(showForm(s.getAttribute("DCRDate").toString(),s.getAttribute("DCREmpName").toString(),s.getAttribute("DCRHQName").toString(),s.getAttribute("DCRNetID").toString(),s.getAttribute("DCREmp").toString(),Integer.parseInt(s.getAttribute("DCRNetIDLevel").toString()),serial,prefix,officeuser,s.getAttribute("DCRNetIDJoinDate").toString(),wwith,wnetid,changeupr,dbname,con,(Vector)s.getAttribute("EmpVector"),flag));
                        }
                        else
                               out.println(showForm(s.getAttribute("DCRDate").toString(),s.getAttribute("DCREmpName").toString(),s.getAttribute("DCRHQName").toString(),s.getAttribute("DCRNetID").toString(),s.getAttribute("DCREmp").toString(),Integer.parseInt(s.getAttribute("DCRNetIDLevel").toString()),serial,prefix,officeuser,s.getAttribute("DCRNetIDJoinDate").toString(),wwith,wnetid,changeupr,dbname,con,(Vector)s.getAttribute("EmpVector"),flag));
                    }
                }
                else
                {
                    out.println("\n<br><br><center><h2><u><font color=red>No "+pname003+"entries found</font></u></h2>");
                    out.println("\n<br><font face=arial size=2>No  "+pname003+" entries were found for this network.");
                    out.println("\n<br>You may not enter your DCR details till the "+pname003+" file is updated.</font>");
                    out.println("<br><br><input type=button class=buttoncolor value=Close onClick=window.close()>");
                } // else for not noTCPEntries closes
            } // if level=1 closes
            else
            {
                if(DateUtils.isAfter(s.getAttribute("DCRDate").toString(),currdate))
                {
                    out.println("<br><br><br><center><font face=arial color=red size=4><b>ALERT!!!</b></font>");
                    out.println("<br><br><center><font face=arial color=red size=4><b>SORRY,YOU CAN NOT ENTER DCR GREATER THAN CURRENT DATE</b></font>");
                    out.println("<br><br><br> <input type=button class=buttoncolor value=Close onClick=window.close()>");
                }
                else
                {
                    if(checkdcrdate(con,s.getAttribute("DCREmp").toString()))
                      {
                    String gracedays="";
                    String val = "";
                                           //********************************************
                                           try
                                             {
                                               Statement stmt = con.createStatement();

                                               String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";

                                               ResultSet rs = stmt.executeQuery(sql);
                                               while(rs.next())
                                                    {
                                                       val = rs.getString("SVarTxt");
                                                    }

                                                           con.setAutoCommit(false);       //added by sagar
                                                           con.commit();//added by sagar
                                                           rs.close();
                                               StringTokenizer st = new StringTokenizer(val,",");
                                               while(st.hasMoreTokens())
                                                {
                                                    st.nextToken();
                                                    gracedays=st.nextToken();
                                                }
                                              }
                                             catch(SQLException e)
                                              { }

                                           //********************************************
                                           if(!val.equalsIgnoreCase("0,0"))
                                             {
                                           out.println("<table class=linktable align=center width=100%><tr>" +
                                                       "<td><b><center>DCR ENTRY SCREEN</center></b></td></tr></table>");

                                         //  out.println("<center> <br><br><br><br><br><br><br> " +
                                         // "<font size=3 face=arial color=red>Your Can Not Filled DCR Because You Doesn't Filled The DCR From "+gracedays+" Or more Days ");
                                         //  out.println("<br>Please Tell Your Manager To Contact Your Head Office for Unlocking DCR.</font> </b>");
                                          out.println("<center> <br><br><br><br><br><br><br> " +
                                                      "<font size=3 face=arial color=red> YOUR DCR IS BLOCKED SINCE THE SAME IS NOT FILLED FOR "+gracedays+" OR ");
                                          out.println("<br>MORE DAYS PLEASE CONTACT YOUR MANAGER</font> </b>");

                                          out.println("<br><br><br><a href=\"#\" class=\"hypclose\" onClick=window.close()>Close this window</a></center>");
                                           }
                                         else
                                            out.println(showForm(s.getAttribute("DCRDate").toString(),s.getAttribute("DCREmpName").toString(),s.getAttribute("DCRHQName").toString(),s.getAttribute("DCRNetID").toString(),s.getAttribute("DCREmp").toString(),Integer.parseInt(s.getAttribute("DCRNetIDLevel").toString()),serial,prefix,officeuser,s.getAttribute("DCRNetIDJoinDate").toString(),wwith,wnetid,changeupr,dbname,con,(Vector)s.getAttribute("EmpVector"),flag));
                            }
                        else
                            out.println(showForm(s.getAttribute("DCRDate").toString(),s.getAttribute("DCREmpName").toString(),s.getAttribute("DCRHQName").toString(),s.getAttribute("DCRNetID").toString(),s.getAttribute("DCREmp").toString(),Integer.parseInt(s.getAttribute("DCRNetIDLevel").toString()),serial,prefix,officeuser,s.getAttribute("DCRNetIDJoinDate").toString(),wwith,wnetid,changeupr,dbname,con,(Vector)s.getAttribute("EmpVector"),flag));
                }
            }
         } //removed frm here
         //***********************************************************************
                Log.log(" 788 : "+s.getAttribute("EmpCode").toString());
		}// else closes
        }
        catch(Exception ex)
    {
        Log.log("Exception in doget",ex.toString());
         //Log.printStackTrace(ex);
            //Log.log("1"+ex.printStackTrace(out));
    }

    finally
    {
        try
        {
            if(con!=null)
                con.close();
        }
        catch(Exception e)
        {
            Log.log("Finally Block ",e.toString());
        }
    }



	}// doGet closes

    /** Shows the form for field staff, without the option to choose the DCR date.
	@param dcrdate The auto-generated DCR date for this employee, based on his previous entries.
	@param ename The employee's name.
	@param hname The headquarter to which the employee belongs.
	@param netid The netid for perticular employee in netmst.
	@param empcode The employee code for which dcr is being entered.
	@param emplvl The employee level for which dcr is being entered.(ie MR Level or Upper Level)
	@param serial The Serial number for the Doctor/Chemist +serial from dcrd file.
	@param prefix The DR/CH/EX For doctor/chemist/expenses as per the radio button for every record.
	**/

	private synchronized String showForm(String dcrdate,String ename,String hname,String netid,String empcode,int emplvl,String serial,String prefix,boolean officeuser,String dcrnetidjoindate,String wwith,String wnetid,String changeupr,String dbname,Connection con,Vector ecode,String flag)
	{
		/*
          Here enter code to get worked with on criteria basis.
    	  ecodes represent the emp code for all upper levels (as well as lower levels in case of Upper Level DCR Entry),
          enames represent the name of employees which are present in ecodes,
		  hq represent hq name for the the netids of perticular rempcodes.
	      hidden field dcrdate :- TO STORE DCR DATE FOR THE EMPCODE.
          hidden field lastwwith :- TO STORE VALUES IN SELECT FIELD OF  DIV TAG IN DOCTORS OR CHEMIST OR AREA ONLY WWITH SELECTION FIELD.
          hidden field lastarea :- TO STORE VALUES IN SELECT FIELD OF  DIV TAG IN DOCTORS OR CHEMIST  ONLY  AREA SELECTION FIELD.
	      dcrno :- DCRnumber will return the maximum number for the dcr entry from DCRH file for perticular (employee code+dcrdate) criteria.
 		  boolean flag:- if nettrans.level1,2,3,4,5 =="" then flag will be false.
	      boolean drchshow :- drchshow will return true only if drchcallsflg='Y' and allowmultiple area='Y'.
          otherwise it will hide the information to add nonlisted doctors/listed doctors as well as
          it will hide the information to add nonlisted chemist/listed chemist.
		  boolean flagdrch :- flagdrch will return true where allowmultiple area 'Y' ie it will show all area in AREA field having allowmultiple area ie DR/CH
	    */
		StringBuffer sb = new StringBuffer();
	    sb.append("<table class=linktable align=center width=100%><tr>" +
                  "<td><b><center>DCR ENTRY SCREEN</center></b></td></tr></table>");
        sb.append("<table class=linktable width=100%>"+
                "<tr><td><font color=black size=3><b>DCR DATE: </font><font color=red size=3><b>"+DateUtils.toDMY(dcrdate)+"</b><font color=black size=3 color=red><b>("+DateUtils.getDay(dcrdate)+")</font></font>"+
                "<td><font color=black><b>NAME: </font>" +
                "<font color=red><b>"+empcode+"</b></font>"+
                "<font color=red><b>&nbsp;&nbsp;"+ename.toUpperCase()+"</b></font>"+
                "<td><font color=black><b>HQ: </font><font color=red><b>"+netid+"</b></font>"+
                "<font color=red><b>&nbsp;&nbsp;"+hname.toUpperCase()+"</b></font>"+
                "</tr>");
        sb.append("</table>");
        String dcrhdata[] = getDCRHData(empcode,dcrdate,dbname);
        try
		{
            boolean LeaveFlag=false;
            String LWrkFlg="";

            if(LeaveChecking(con))
            {
            String leavetaken ="";
            //Log.log("select * from leaverequest inner join leavemapping on leaverequest.Leaveid=leavemapping.Leaveid where '"+dcrdate+"'>=startdate and '"+dcrdate+"'<=enddate and leaverequest.ecode='"+empcode+"' and status='Approved' ");
            ResultSet rsleavetaken=con.createStatement().executeQuery("select * from leaverequest inner join leavemapping on leaverequest.leaveid=leavemapping.leaveid where '"+dcrdate+"'>=startdate and '"+dcrdate+"'<=enddate and leaverequest.ecode='"+empcode+"' and status='Approved' ");
            if(rsleavetaken.next())
            {
                LeaveFlag=true;
                leavetaken=rsleavetaken.getString("LType");

                if(leavetaken.equalsIgnoreCase("PL"))
                   leavetaken="LEAVE PRIVILEG";
                else if(leavetaken.equalsIgnoreCase("SL"))
                   leavetaken="LEAVE SICK";
                else if(leavetaken.equalsIgnoreCase("CL"))
                   leavetaken="LEAVE CASUAL";
                else if(leavetaken.equalsIgnoreCase("LOP"))
                   leavetaken="LEAVE LOP";
            }

            //Log.log("785 : LeaveTaken : "+leavetaken);
            if(!leavetaken.equalsIgnoreCase(""))
            {
               rsleavetaken=con.createStatement().executeQuery("select wrkflgcd,wrktype from wrkmst where wrkflgdesc='"+leavetaken+"' ");
               //Log.log("787 : select wrkflgcd,wrktype from wrkmst where wrkflgdesc='"+leavetaken+"'");
               if(rsleavetaken.next())
                  LWrkFlg=rsleavetaken.getString(1);
            }
            }

            sb.append("<body onLoad=globaleCriteria('"+prefix+"')>");
            sb.append("<form name=dcrh method=Post>");
            sb.append("<input type=hidden name=flag value=\""+flag+"\">");
            sb.append("<input type=hidden name=lastwwith value=\"\">");
            sb.append("<input type=hidden name=lastarea value=\"\">");
           //sb.append("<input type=hidden name=jntwrk  value=\"\">");
            String holdate  = getHoliDay(dcrdate,empcode,dbname);
            holdate = holdate.substring(1,holdate.length());
            ArrayList al = new ArrayList();
            if(!holdate.equalsIgnoreCase(""))
            {
                sb.append("<table class=linktable align=center width=100%><tr>" +
                        "<td><b><font color=red>Do You Want To Enter DCR For The Date : </font><select name=selholmst class=listbox></b>");
                int i = holdate.length()/11;
                String holdates = "";
                for(int k=0;k<i;k++)
                {
                    int j = 11 * (k+1);
                    int p = 11 * k;
                    holdates = holdate.substring(p,j-1);
                    al.add(holdates);
                }

                ListIterator litr = al.listIterator();
                while(litr.hasNext())
                {
                    Object element = litr.next();
                    litr.set(element+"");
                }

                while(litr.hasPrevious())
                {
                    Object element = litr.previous();
                    sb.append("<option value="+element+">"+DateUtils.toDMY(String.valueOf(element)));
                }

                sb.append("</select>");
                sb.append("<input type=button class=buttoncolor name=entholidaydcr value=Submit onclick=enterHolidayDCR('"+servPath+"',"+empcode+")>");
                sb.append("</td></tr></table>");
            }

            String dcrno = dcrhdata[4];
            String dcrhtcpid = dcrhdata[5];
            String mon = DateUtils.fillMonthNumber(DateUtils.toDMY(dcrdate).substring((DateUtils.toDMY(dcrdate).indexOf("/"))+1,DateUtils.toDMY(dcrdate).lastIndexOf("/")));
            String yr = DateUtils.toDMY(dcrdate).substring(DateUtils.toDMY(dcrdate).lastIndexOf("/")+1,DateUtils.toDMY(dcrdate).length());

            Vector wwithdca = getWWithVector(yr,mon,netid,empcode,dcrnetidjoindate,emplvl,dbname);
            Vector wnetiddc = getWNetidVector(yr,mon,empcode,emplvl,dbname);
            Vector doctordetails = new Vector();
            Vector doctordetailsSRFC = new Vector();
            Vector chemistdetails = new Vector();
            Vector expensedetails = new Vector();

            Vector wwithn = new Vector();
            //if(dcrno.equalsIgnoreCase(""))
                //doctordetailsSRFC = getSRFCMTP(dcrdate,netid,dbname);

          /*  if(dcrno.equalsIgnoreCase(""))
            {
                getSRFCMTP(dcrdate,netid,dbname,empcode);
                String dcrhdata1[] = getDCRHData(empcode,dcrdate,dbname);
                dcrno=dcrhdata1[4];
                dcrhtcpid = dcrhdata1[5];
            }
         */

            if(!dcrno.equalsIgnoreCase(""))
            {
                doctordetails = getDoctorDetailsVector(dcrno,dbname,dcrdate);
                chemistdetails = getChemistDetailsVector(dcrno,dbname);
                expensedetails = getExpenseDetailsVector(dcrno,emplvl,dbname);
            }
            Vector tempvector ;
            String tcpid = "";
            if(doctordetails.size()>0)
            {
                tempvector = (Vector)doctordetails.lastElement();
                if(!changeupr.equalsIgnoreCase("yes"))
                {
                    wwith = tempvector.elementAt(3).toString();
                    wwithn.add(tempvector.elementAt(3).toString());
                    wwithn.add(tempvector.elementAt(6).toString());
                    wwithn.add(tempvector.elementAt(7).toString());
                    wwithn.add(tempvector.elementAt(8).toString());
                    wnetid = tempvector.elementAt(4).toString();
                }
                tcpid = tempvector.elementAt(5).toString();
            }
            else if(chemistdetails.size()>0)
            {
                tempvector = (Vector)chemistdetails.lastElement();
                if(!changeupr.equalsIgnoreCase("yes"))
                {
                    wwith = tempvector.elementAt(3).toString();
                    wwithn.add(tempvector.elementAt(3).toString());
                    wwithn.add(tempvector.elementAt(6).toString());
                    wwithn.add(tempvector.elementAt(7).toString());
                    wwithn.add(tempvector.elementAt(8).toString());
                    wnetid = tempvector.elementAt(4).toString();
                }
                tcpid = tempvector.elementAt(5).toString();
            }

            if((emplvl > 1) && wnetid.equalsIgnoreCase(""))
            {
				if(wnetiddc.size()==0)
			    {
					sb.append("<center><font color=red size=3><b>WARNING : Incomplete Network Lower hierarchy</b></font></center>");
		        }
	            else
			    {
                    tempvector = (Vector)wnetiddc.firstElement();
                    wnetid = tempvector.elementAt(0).toString();
		        }
            }

            if(emplvl==1 && wnetid.equalsIgnoreCase(""))
              wnetid = netid;

            //area vector
            Vector areadc = getAreaDCVector(wnetid,dbname,dcrdate);
            boolean drchexist = false;

            if(doctordetails.size() > 0 || chemistdetails.size() > 0)
                drchexist = true;

            // in this case tcp is taken from netid of mr or netid od upper level (non working area)
            Vector areaa = getAreaAVector(netid,drchexist,dbname);
            if(officeuser)
            {
                String rcptdate = dcrhdata[0];
                sb.append("<table width=100% class=linktable align=center><tr><td width=33%><td width=33%><td class=linktabledata>");
                sb.append("ReceiptDate : <input type=text name=recd size=2 maxlength=2 class=textbox value="+rcptdate.substring(rcptdate.lastIndexOf("-")+1,rcptdate.length())+"> / " +
                    "<input type=text name=recm size=2 maxlength=2 class=textbox  value="+rcptdate.substring(rcptdate.indexOf("-")+1,rcptdate.lastIndexOf("-"))+"> / " +
                    "<input type=text name=recy size=4 maxlength=4 class=textbox value="+rcptdate.substring(0,rcptdate.indexOf("-"))+">");
                sb.append("</td></tr></table>");
            }
            else
            {
                sb.append("<input type=hidden name=recd> " +
                          "<input type=hidden name=recm> " +
                          "<input type=hidden name=recy>");
            }
 //doctor data tab  starts here

            // DIV TAB WHICH IS ADDED LATER ON STARTS HERE
            sb.append("<div class=\"tab-pane\" id=\"tabPane1\">");
            sb.append("<div class=\"tab-page\" id=\"tabPage1\">");
            sb.append("<h2 class=\"tab\">Doctors Data</h2>");
            sb.append("<table width=97% cellspacing=0><tr>");
            sb.append(getHeading(emplvl,1,dbname));

            String flgbtn="";
            if(flag.equalsIgnoreCase("0"))
            {
            flgbtn="false";
            //sb.append("<tr><td><input type=checkbox checked id=wwith name=wwith  size=4  class=checkbox onClick=setjnwrk() >Independent");
            //sb.append("<tr><td><input type=button class=buttoncolor value='JointWorking' onClick=addjointworking('"+servPath+"','"+wwithdca+"','"+dcrno+"')>");
            //sb.append("<tr><td><input name=jnwrk disabled=true type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            }
            else
            {
                flgbtn="true";
            }

            if(ecode.size()==0)
            {
               sb.append("<tr><td><input type=checkbox  name=chkIndep size=4 class=checkbox onClick=javascript:setjnwrk()>Independent");
            }
            if(wwithdca.size()==0)
            {
               sb.append("<tr><td><input type=checkbox  checked name=chkIndep size=4 class=checkbox onClick=javascript:setjnwrk()>Independent");
            }

            if(flag.equalsIgnoreCase("0") && ecode.size()==0 && wwithdca.size()==0)
               sb.append("<tr><td><input type=checkbox  name=chkIndep size=4 class=checkbox onClick=javascript:setjnwrk()>Independent");
            else if(!flag.equalsIgnoreCase("0") && ecode.size()!=0 && wwithdca.size()!=0)
               sb.append("<tr><td><input type=checkbox name=chkIndep size=4 class=checkbox onClick=javascript:setjnwrk()>Independent");

            if(flag.equalsIgnoreCase("1"))
               sb.append("<tr><td><input name=jnwrk disabled=\""+flgbtn+"\" type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"') >");
            else
               sb.append("<tr><td><input name=jnwrk type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");

            if(!flag.equalsIgnoreCase("1"))
            {
                 if(ecode.size()>0)
                 {
                   sb.append(getWWithListBox(ecode,wwithn,1));
                 }
                 else
                 {
                   sb.append(getWWithListBox(wwithdca,wwithn,1,flag));
                 }
            }
            // sb.append(getWWithList(wwithdca,wwithn,1,dcrno,dcrdate,empcode,dcrnetidjoindate,netid,emplvl,dbname));
            // sb.append(4(wwithdca,wwithn,1));

            if(emplvl!=1)
            {
                sb.append(getWNetidListBox(wnetiddc,wnetid,1));
            }
            // area
            if(tcpid.equalsIgnoreCase(""))
               sb.append(getareaListBox(areadc,getTcpidMTP(dcrdate,netid,dbname,empcode),1,emplvl));
            else
               sb.append(getareaListBox(areadc,tcpid,1,emplvl));

            //sb.append(getareaListBox(areadc,getTcpidMTP(dcrdate,netid,dbname),1));
            String drchshow ="Y";

            //doctors data start here
            if(areadc.size()==0)
            {
                sb.append("<td colspan=3><font face=verdana size=2 color=red>You can not add Doctor without towns</font></td></tr>");
            }
            else
            {
                if(checkForNonMslDoc(con))
                   sb.append("&nbsp;&nbsp;<font face=verdana size=1 color=black>Nos.:<input type=text class=textbox name=noz maxlength=2 value='' size=2 style='background-Color=white' disabled=true>" +
                        "<input type=button name=nd1 class=buttoncolor disabled=true value='Add Non " + pname001 + " Doctors' onClick=addNonEdlD('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')>" +
                        "<select name=vsttime class=listboxred><option value=\"M\">M</option><option value=\"E\">E</option></select></td></tr>");
                sb.append("<td colspan=3><font face=verdana size=1 color=black>SEARCH CRITERIA:");
                sb.append("<input type=radio checked name=searchd value='aw' onClick=\"document.dcrh.filterd.value='';document.dcrh.filterd.readOnly=true;\">" );
                 if(emplvl==1)
                 {
                    sb.append(" SCH Wise<font face=verdana size=1 color=black>" );
                 }
                else
                 {
                    sb.append(" SCH Wise<font checked face=verdana size=1 color=black>" );
                 }

                sb.append("<input disabled type=radio name=searchd value='wc' onClick=\"document.dcrh.filterd.readOnly=false;document.dcrh.filterd.focus();\">" +
                          "Wildcard Search:<input readOnly=true type=text class=textbox name=filterd size=15 style='background-Color=white'><font face=verdana size=1 color=black>");
                sb.append("<input disabled type=radio  name=searchd value='al' onClick=\"document.dcrh.filterd.value='';document.dcrh.filterd.readOnly=true;\">ALL");
                if(emplvl==1)
                {
                     sb.append("<input type=radio checked name=searchd value='mdr' onClick=\"document.dcrh.filterd.value='';document.dcrh.filterd.readOnly=true;\">" +
                     " Missed Dr<font face=verdana size=1 color=black>");
                }

                sb.append("<font class=tbltextmfs><input type=button name=docentry class=buttoncolor value='Doctors Entry' onClick=drchkjw(document.forms[0].wwithd.value,document.forms[0].tcpidd.value,'"+LeaveFlag+"','"+LWrkFlg+"')></font>");//confirm(\"Joint_Working_Selected?\");searchdoc('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"')        //drchkjw(document.forms[0].wwithd.value)

                //sb.append(getSCH());
                sb.append("<script language=javascript type=text/javascript>" +
                        "function getName()"+
                        "{"+
                        "var selObj = document.getElementById('wwith');"+
                        "var i;"+
                        "var count=0;"+
                        "var url='';   "+
                        "for (i=0; i<selObj.options.length; i++) {"+
                    "if (selObj.options[i].selected) {"+
                    "url=url+getAN(\"t\"+selObj.options[i].value)+',';"+

                    "}"+
                "}"+
                "url='All doctor Selected will be considered as joint work with '+url;"+
                "var m=confirm(url);" +
                        "if(m==true)" +
                        "   {" +
                        "   searchdoc('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')" +
                        "   }" +
                    "}" +
                 "</script>");


                sb.append("<script language=javascript type=text/javascript>" +
                        "function getAN(code)" +
                        "{" +
                        "var m='';" +
                        "alert(code);" +
                        "m=eval(\"document.forms[0].\"+code+\".value\");" +
                        "alert(m);" +
                        "return m;" +
                        "}" +
                        "</script>");

                sb.append("<script language=javascript type=text/javascript>" +
                        "function drchkjw(name,area,LeaveFlag,LWrkFlg)" +
                        "{" +
                        "if(area=='Select Schedule')"+
                        "{"+
                        "alert('Please select schedule'); return false;"+
                         "} "+
                        "var a='';" +
                        "if(name =='Independent') " +
                        "a='All doctor Selected will be considered as Independent';" +
                        " else if(name!='')  " +
                        "a='All doctor Selected will be considered as Joint Working';" +
                        " else " +
                        "a='No Joint Working Selected'; "+
                        "var m=confirm(a);" +
                        "" +
                        "if(m==true)" +
                        "   {" +
                        "   searchdoc('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')" +
                        "   }" +
                        "}" +
                        "</script>");

                sb.append("<script language=javascript type=text/javascript>" +
                        "function chchkjw(name,area,LeaveFlag,LWrkFlg)" +
                        "{" +
                         "if(area=='Select Schedule')"+
                        " {"+
                        "alert('Please select schedule'); return false;"+
                         "} "+
                        "var a='';" +
                        "if(name =='Independent') " +
                        "a='All chemists Selected will be considered as Independent';" +
                        " else if(name!='')  " +
                        "a='All chemists Selected will be considered as Joint Working';" +
                        " else " +
                        "a='No Joint Working Selected'; " +
                        "var m=confirm(a);" +
                        "" +
                        "if(m==true)" +
                        "   {" +
                        "   searchchem('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')" +
                        "   }" +
                        "}" +
                        "</script>");
                sb.append("</td></tr>");
            }
            sb.append("</table>");
            sb.append("<table width=97% class=linktable cellspacing=1>");
            sb.append(getDoctorDetails(doctordetails,serial,dcrhtcpid,dcrno,dbname,con,empcode,doctordetailsSRFC));
            sb.append("</table>");
            sb.append("</div>");

 // CHEMIST DATA TAB STARTS HERE
            sb.append("<div class=\"tab-page\" id=\"tabPage2\">");
            sb.append("<h2 class=\"tab\">Chemists Data</h2>");
            sb.append("<table width=97% cellspacing=1><tr>");

            sb.append(getHeading(emplvl,2,dbname));

            if(flag.equalsIgnoreCase("0"))
            {
            flgbtn="false";
            //sb.append("<tr><td><input type=checkbox checked id=wwith name=wwith  size=4  class=checkbox onClick=setjnwrk() >Independent");
            //sb.append("<tr><td><input type=button class=buttoncolor value='JointWorking' onClick=addjointworking('"+servPath+"','"+wwithdca+"','"+dcrno+"')>");
            //sb.append("<tr><td><input name=jnwrk disabled=true type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            }
            else
            {
                flgbtn="true";
            }
            if(ecode.size()==0)
            {
               sb.append("<tr><td><input type=checkbox  name=chkIndepc size=4 class=checkbox onClick=javascript:setjnwrkc()>Independent");
            }
            if(wwithdca.size()==0)
            {
               sb.append("<tr><td><input type=checkbox  name=chkIndepc size=4 class=checkbox onClick=javascript:setjnwrkc()>Independent");
            }

            if(flag.equalsIgnoreCase("0")&&ecode.size()==0 &&wwithdca.size()==0)
               sb.append("<tr><td><input type=checkbox name=chkIndepc size=4 class=checkbox onClick=javascript:setjnwrkc()>Independent");
            else if(!flag.equalsIgnoreCase("0")&&ecode.size()!=0 &&wwithdca.size()!=0)
               sb.append("<tr><td><input type=checkbox  name=chkIndepc size=4 class=checkbox onClick=javascript:setjnwrkc()>Independent");

            if(flag.equalsIgnoreCase("1"))
                sb.append("<tr><td><input name=jnwrkc disabled=\""+flgbtn+"\" type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"') >");
            else
                sb.append("<tr><td><input name=jnwrkc type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");

           // sb.append("<tr><td><input name=jnwrkc type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            if(!flag.equalsIgnoreCase("1"))
            {
            if(ecode.size()>0)
               sb.append(getWWithListBox(ecode,wwithn,2));
            else
               sb.append(getWWithListBox(wwithdca,wwithn,2,flag));
            }

           /*

           // sb.append("<tr><td><input type=checkbox checked name=wwithchem size=4  class=checkbox onClick=javascript:setjnwrk() >Independent");
                                   //sb.append("<tr><td><input type=checkbox checked id=wwith name=wwith  size=4  class=checkbox onClick=setjnwrk() >Independent");
                                  //sb.append("<tr><td><input type=button class=buttoncolor value='JointWorking' onClick=addjointworking('"+servPath+"','"+wwithdca+"','"+dcrno+"')>");

            sb.append("<tr><td><input name=jnwrkc  disabled=\""+flgbtn+"\" type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");

           // sb.append(getWWithListBox(wwithdca,wwithn,2,dcrno,dcrdate,empcode,dcrnetidjoindate,netid,emplvl,dbname));
          //  sb.append(getWWithListBox(wwithdca,wwithn,2));
             if(ecode.size()>0)
                 sb.append(getWWithListBox(ecode,wwithn,2));
            else
                 sb.append(getWWithListBox(wwithdca,wwithn,2,flag));
           */

            if(emplvl!=1)
                sb.append(getWNetidListBox(wnetiddc,wnetid,2));

            sb.append(getareaListBox(areadc,tcpid,2,emplvl));
            //sb.append(getareaListBox(areadc,getTcpidMTP(dcrdate,netid,dbname),2));
            if(areadc.size()==0)
            {
                sb.append("<td colspan=3> <font face=verdana size=2 color=red>You can not add Chemist without towns</font></td></tr>");
            }
            else
            {
                if(checkForNonMslDoc(con))
                sb.append("&nbsp;&nbsp;<font face=verdana size=1 color=black>Nos.:<input type=text class=textbox name=nos maxlength=2 value='' size=2 style='background-Color=white'>" +
                        "<input type=button name=nc1 class=buttoncolor value='Add Non  " + pname001 + " Chemist' onclick=addNonEdlC('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')></td>");

                sb.append("<tr><td colspan=3><font face=verdana size=1 color=black>SEARCH CRITERIA:<input type=radio name=search value='aw' onClick=\"document.dcrh.filter.value='';document.dcrh.filter.readOnly=true;\">SCH Wise<font face=verdana size=1 color=black>" +
                        " <input type=radio disabled name=search value='wc' onClick=\"document.dcrh.filter.readOnly=false;document.dcrh.filter.focus();\">Wildcard Search:<input type=text class=textbox name=filter readOnly=true value='' size=15 style='background-Color=white'>" +
                        " <font face=verdana size=1 color=black> <input type=radio checked name=search value='al' onClick=\"document.dcrh.filter.value='';document.dcrh.filter.readOnly=true;\">ALL");
                sb.append("<font class=tbltextmfs><input type=button name=chementry class=buttoncolor value='Chemists Entry' onClick=chchkjw(document.forms[0].wwithc.value,document.forms[0].tcpidc.value,'"+LeaveFlag+"','"+LWrkFlg+"')></font>&nbsp;&nbsp;</td></tr>");//confirm(\"Joint_Working_Selected?\");searchchem('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"')//chemistchkjw()
            }

            sb.append("<script language=javascript type=text/javascript>" +
                        "function chemistchkjw()" +
                        "{" +
                        "var m=confirm('Is Joint Working Selection Done ?');" +
                        "if(m==true)" +
                        "   {" +
                        "   searchchem('"+servPath+"','"+dcrno+"','"+dcrhtcpid+"','"+LeaveFlag+"','"+LWrkFlg+"')" +
                        "   }" +
                        "}" +
                        "</script>");

            sb.append("</table>");
            sb.append("<table width=97% class=linktable cellspacing=1>");
            sb.append(getChemistDetails(chemistdetails,serial,dcrhtcpid,dcrno));
            sb.append("</table>");
            sb.append("</div>");

            // EXPENSES ENTRY STARTS HERE

            sb.append("<div class=\"tab-page\" id=\"tabPage3\">");
            sb.append("<h2 class=\"tab\">Expense Data</h2>");

            int type=emplvl;

            if(emplvl>1)
                type=2;

            sb.append("<table width=97%>");
            if(!dcrno.equalsIgnoreCase(""))
                sb.append("<tr><td width=100%><font class=tbltextmfs><center><input type=button name=expentry class=buttoncolor value='Expenses Entry' onClick=expenseEntry('"+servPath+"',"+type+",'"+dcrno+"','"+netid+"','"+tcpid+"')></center></font></td></tr>");

            sb.append("</table>");
            sb.append("<table width=97% class=linktable>");
            sb.append(getExpenseDetails(expensedetails,emplvl,dcrhdata[1],dcrhdata[2],dcrhdata[3]));
            sb.append("</table>");

            sb.append("<table align=\"center\">");
            sb.append("<tr><td><font class=\"tbltextmfs\"><center><input type=\"button\" name=\"expsentrye\" class=\"buttoncolor\" value='Delete' onClick=deleteConfirmEX('"+servPath+"')></center></font></td></tr>");
            sb.append("</table>");
            sb.append("</div>");

            //Area which are non working can be added from here

            sb.append("<div class=\"tab-page\" id=\"tabPage4\">");
            sb.append("<h2 class=\"tab\">Others</h2>");
            sb.append("<table width=\"97%\">");
            sb.append(getHeading(emplvl,3,dbname));

            if(flag.equalsIgnoreCase("0"))
            {
            flgbtn="false";
            }
            else
            {
                flgbtn="true";
            }
            if(ecode.size()==0)
            {
               sb.append("<tr><td><input type=checkbox  name=chkIndepo size=4 class=checkbox onClick=javascript:setjnwrko()>Independent");
            }
            if(wwithdca.size()==0)
            {
               sb.append("<tr><td><input type=checkbox checked name=chkIndepo size=4 class=checkbox onClick=javascript:setjnwrko()>Independent");
            }

            if(flag.equalsIgnoreCase("0")&&ecode.size()==0 &&wwithdca.size()==0)
                  sb.append("<tr><td><input type=checkbox  name=chkIndepo size=4 class=checkbox onClick=javascript:setjnwrko()>Independent");
            else if(!flag.equalsIgnoreCase("0")&&ecode.size()!=0 &&wwithdca.size()!=0)
                       sb.append("<tr><td><input type=checkbox name=chkIndepo size=4 class=checkbox onClick=javascript:setjnwrko()>Independent");

            if(flag.equalsIgnoreCase("1"))
                  sb.append("<tr><td><input name=jnwrko disabled=\""+flgbtn+"\" type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"') >");
            else
                  sb.append("<tr><td><input name=jnwrko type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");



           //  sb.append("<tr><td><input name=jnwrko type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            if(!flag.equalsIgnoreCase("1"))
            {
            if(ecode.size()>0)
               sb.append(getWWithListBox(ecode,wwithn,3));
            else
               sb.append(getWWithListBox(wwithdca,wwithn,3,flag));
            }

            /*sara

           // sb.append("<tr><td><input type=checkbox checked  name=wwithoth size=4  class=checkbox onClick=javascript:setjnwrk()>Independent");
                                   //sb.append("<tr><td><input type=checkbox checked id=wwith name=wwith  size=4  class=checkbox onClick=setjnwrk() >Independent");
                                  //sb.append("<tr><td><input type=button class=buttoncolor value='JointWorking' onClick=addjointworking('"+servPath+"','"+wwithdca+"','"+dcrno+"')>");
              sb.append("<tr><td><input name=jnwrkoth type=button class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            //sb.append(getWWithListBox(wwithdca,wwithn,3,dcrno,dcrdate,empcode,dcrnetidjoindate,netid,emplvl,dbname));
            // sb.append(getWWithListBox(wwithdca,wwithn,3));

            if(ecode.size()>0)
                 sb.append(getWWithListBox(ecode,wwithn,3));
            else
                 sb.append(getWWithListBox(wwithdca,wwithn,3,flag));
           */

            sb.append(getnonfieldworkareaListBox(areaa,tcpid));

            if(areaa.size() > 0)
                sb.append("<input type=\"button\" name=\"nonwrkarea\" class=\"buttoncolor\" value='Add Non Field Work Type' onClick=nonWorkingArea('"+servPath+"','"+dcrno+"','"+LeaveFlag+"','"+LWrkFlg+"')></center></font>");
            else
                sb.append("<input type=\"button\" name=\"nonwrkarea\" disabled=\"true\" class=\"buttoncolor\" value='Add Non Field Work Type'></center></font>");

            sb.append("</table>");

            if(emplvl==1)
            {
                sb.append(areaDetailsMRLevel(drchshow,dcrno,netid,dbname));
            }
            else
            {
                sb.append(areaDetailsUPRLevel(drchshow,dcrno,dbname));
            }

            sb.append("</div>");

            sb.append("</div>");

            sb.append("</table><table class=linktable border=0 width=100%>");

            sb.append(getRemark(dcrno,dbname));

            //if(tcpDrchk.equalsIgnoreCase("Y"))
                sb.append(getReason(dcrno,dbname,dcrdate,netid));

            String valexists="";
            String valexists1="";
            String valexists2="";
            InfoHelper ih = null;
            ih = new InfoHelper(con,empcode,dbname);
            String[] drexpalter={};
            valexists = ih.getsysprmcount("DCRExpense");
            valexists1 = ih.getsysprmcount("DCRProdChk");
            valexists2 = ih.getsysprmcount("DCRVisitChk");

            String DrchExpChk=getcheckDrChExp(dcrno,con);
            String Expchk=getcheckExp(dcrno,con);

            String tpid=getcheckTcpid(dcrno,con);

           String curryr = dcrdate.substring(0,4);
           String currmth = dcrdate.substring(5,7);
           if(currmth.substring(1,2).equalsIgnoreCase("-"))
              currmth="0"+dcrdate.substring(5,6);

           String dcrdateyrmth = curryr+currmth;

            String drsample=getdrsamplegiven(dcrno,con,dcrdateyrmth);
            String drgift =getdrgiftgiven(dcrno,con,dcrdateyrmth);
            String drwwith=getdrwwith(dcrno,con,dcrdateyrmth);
           // Log.log(drgift+" : "+drsample+" : "+drwwith);

            if((!DrchExpChk.equalsIgnoreCase(""))||!(Expchk.equalsIgnoreCase(""))||!(drsample.equalsIgnoreCase(""))||!(drgift.equalsIgnoreCase(""))||!(drwwith.equalsIgnoreCase("")))
            {
              if(emplvl==1)
                 sb.append("<td width=20% valign=top><font class=tbltextmfs><a  href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
              else
                 sb.append("<td width=20% valign=top><font class=tbltextmfs><a  href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
            }
            else if(ih.remarkExists(dbname,valexists,"DCRExpense",drexpalter,"N").equals("Y"))
            {
            int expensecount=0;
            Statement stmt = con.createStatement();
            String sql = "select count(*) from dcre where dcrno='"+dcrno+"'";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
                {
	            expensecount=rs.getInt(1);
                }

            if(expensecount==0)
                sb.append("<td width=20% valign=top> <font class=tbltextmfs><a href=\"javascript:history.go(0);\") onClick=\"alert('Please fill up expense')\";><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");

            //else
              //  sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+")\")><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
            }
            else if(ih.remarkExists(dbname,valexists1,"DCRProdChk",drexpalter,"N").equals("Y"))
            {
            int drcount=0;
            Statement stmt = con.createStatement();
            String sql = "select serial,cntcd,wnetid from dcrd where dcrno='"+dcrno+"'";
            ResultSet rs = stmt.executeQuery(sql);
                Vector serl=new Vector();
                Vector cntcd=new Vector();
                Vector hcenetid=new Vector();

            while(rs.next())
                {
	            //drcount=rs.getInt(1);
                serl.addElement(rs.getString(1));
                cntcd.addElement(rs.getString(2));
                hcenetid.addElement(rs.getString(3));
                }

                int chk=0;
                String name="";
                for(int i=0;i<serl.size();i++)
                {
                String sql123 = "select count(prodid) from dcrp where serial='"+serl.get(i)+"' and dcrno='"+dcrno+"' ";
                ResultSet rs123 = stmt.executeQuery(sql123);
                    if(rs123.next())
                    {
                        if(rs123.getInt(1)==0)
                        {
                            name +=getDrName(cntcd.get(i).toString(),con,hcenetid.get(i).toString());
                            if(i<serl.size()-1)
                               name +=", ";
                            chk=chk+1;
                        }
                    }
                }
                if(chk!=0)
                {   if(emplvl==1)
                    sb.append("<td width=20% valign=top><font class=tbltextmfs><a onClick=\"return confirm('No Products/Gift Filled for "+name+" ')\"; href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"');\" ><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
                    else
                     sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
                }
                if(chk==0)
                {   if(emplvl==1)
                     sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"')\"><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
                    else
                     sb.append("<td width=20% valign=top><font class=tbltextmfs><a  href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
                }
             }
            else if(ih.remarkExists(dbname,valexists2,"DCRVisitChk",drexpalter,"N").equals("Y"))
            {
            int chk=0;int tmp=0;
            Statement stmt = con.createStatement();
            String sql = "select VisitTime from dcrd where dcrno='"+dcrno+"'";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
                {
                  tmp=1;
                 if(rs.getString(1).equalsIgnoreCase("E"))
                 {
                     chk=1;
                     break;
                 }
                }
                if(chk==0 && tmp==1)
                {   if(emplvl==1)
                     sb.append("<td width=20% valign=top><font class=tbltextmfs><a onClick=\"return confirm('All Visit Time Morning Selected.')\"; href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"');\" ><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
                    else
                     sb.append("<td width=20% valign=top><font class=tbltextmfs><a onClick=\"return confirm('All Visit Time Morning Selected.')\"; href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
                }
                if(chk!=0)
                {   if(emplvl==1)
                    sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"')\"><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
                    else
                    sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red> Confirm DCR Entry </font> </a> </font> </td>");
                }
             }
            else
            {
                if(emplvl==1)
                sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+drsample+"','"+DrchExpChk+"','"+drgift+"','"+drwwith+"')\"><font face=verdana size=2 color=red>Confirm DCR Entry</font></a></font></td>");
                else
                sb.append("<td width=20% valign=top><font class=tbltextmfs><a href=\"javascript:confirmDCR1('"+servPath+"','"+dcrno+"',"+type+",'"+Expchk+"','"+tpid+"','"+DrchExpChk+"');\" ><font face=verdana size=2 color=red>Confirm DCR Entry </font> </a> </font> </td>");
            }
            sb.append("</tr></table>");
            sb.append("<center><a href=javascript:window.close() class=\"hypclose\"><font face=verdana size=2 color=red>close this window</font></a></center>");

		} // try closes
		catch(Exception e)
		{
			//e.printStackTrace(out);
            Log.printStackTrace(e);
		} // catch closes
		return sb.toString();

	}// showForm closes

	/** Checks if entries exist in the TCP file for this netid.
	@param netid The network ID to check for.
	@return true if no entries exist, false otherwise.
	**/

	private boolean noTCPEntries(String netid,String dbname)
	{
		boolean retFlag=false;

		try
		{   Connection con=null;

             con= ConnectionHandler.getConnection(dbname);

            Statement stmt = con.createStatement();
            String sql="SELECT COUNT(0) FROM TCPHED " +
		              "WHERE NETID=\""+netid+"\" AND APRVCODE<>'' ";

			ResultSet rs = stmt.executeQuery(sql);
			rs.next();

            if(rs.getInt(1)==0)
            {retFlag=true; }

            rs.close();
            con.close();
		}
         catch(SQLException e)
		 {
			Log.printStackTrace(e);
         } // catch closes

    		return retFlag;
    }// noTCPEntries closes



	 /**This function returns the dcr area details for MR Level.
        it is only for non field work area which has to be shown.
        @param drchshow The doctor/chemist flag. if "Y"
               then div tag with all neceessary will be show otherwise hidden.
        @param dcrno The DCR number for which dcr is to be entered.
        @param netid The DCR number for which dcr is to be entered, will check for tcpid from tcphed for that netid .
     **/


    private String areaDetailsMRLevel(String drchshow,String dcrno,String netid,String dbname)
    {
        Connection con = null;
        Statement stmt = null;

        StringBuffer sb = new StringBuffer();

        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();

            sb.append("<br><table width=97% class=linktableborder>");

            int k=0;

            if(!dcrno.equalsIgnoreCase(""))
            {
                ResultSet rs = null;

                sb.append("<tr><td class=linktablehead>Area<td class=linktablehead>Type<td class=linktablehead>Delete<td class=linktablehead>Select Area For Expense Calculation");
                boolean chk = false;
                String dcrhtcp="";
                String dcrhtcprs = "" ;

                String nonwrkarea = "SELECT DCRH.TCPID, IF(ISNULL(TCPHED.TOWN),'',TCPHED.TOWN) AS TOWN," +
                        "IF(ISNULL(WRKMST.DRCHCALLSFLG),'',WRKMST.DRCHCALLSFLG) AS DRCHCALLSFLG," +
                        "IF(ISNULL(WRKMST.ALLOWMULTIPLEAREA),'',WRKMST.ALLOWMULTIPLEAREA) " +
                        "FROM DCRH " +
                        "LEFT JOIN TCPHED ON DCRH.NETID = TCPHED.NETID AND TCPHED.TCPID = DCRH.TCPID " +
                        "LEFT JOIN WRKMST ON TCPHED.WTYPE = WRKMST.WRKFLGCD WHERE DCRNO = \""+dcrno+"\" ";
                rs = stmt.executeQuery(nonwrkarea);

                if(rs.next())
                {
                    dcrhtcprs = rs.getString(1);

                    if(rs.getString(4).equalsIgnoreCase("N"))
                        drchshow = "N";
                    chk = true;
                    if(!rs.getString(1).equalsIgnoreCase("000"))
                    {
                        if(rs.getString(3).equalsIgnoreCase("N"))
                        {
                            k++;
                            sb.append("<tr><td class=linktabledata><font face=verdana color=black>"+rs.getString(1).trim()+"</font>\t<font face=verdana color=red>"+rs.getString(2).trim()+"</font>");
                            sb.append("<td class=linktabledata>Non-Working<td class=linktabledata><input type=button name=nonworkarea class=buttoncolor value='Delete' onClick=deleteConfirmAR('A"+rs.getString(1)+"','"+servPath+"')></center></font><td class=linktabledata><font color=red>Selected</font>");
                            sb.append("</tr>");
                        }
                        else
                            dcrhtcp = rs.getString(1);
                    }
                }
                    rs.close();

                if(chk==true)
                {
                    if(!dcrhtcprs.equalsIgnoreCase("000"))
                    {
                        rs = stmt.executeQuery("SELECT DISTINCT DCRD.TCPID,IF(ISNULL(TCPHED.TOWN),'',TCPHED.TOWN) AS TOWN," +
                                               "IF(ISNULL(WRKMST.DRCHCALLSFLG),'',WRKMST.DRCHCALLSFLG) AS DRCHCALLSFLG " +
                                               "FROM DCRD " +
                                               "LEFT JOIN TCPHED ON TCPHED.NETID=\""+netid+"\" AND TCPHED.TCPID=DCRD.TCPID " +
                                               "LEFT JOIN WRKMST ON TCPHED.WTYPE = WRKMST.WRKFLGCD " +
                                               "WHERE DCRD.DCRNO=\""+dcrno+"\" ORDER BY WRKMST.DRCHCALLSFLG ");

                        while(rs.next())
                        {
                            k++;
                            sb.append("<tr>");
                            sb.append("<td class=linktabledata><font face=verdana color=black>"+rs.getString(1).trim()+"</font>\t<font face=verdana color=red>"+rs.getString(2).trim()+"</font>");
                            sb.append("<td class=linktabledata>Working<td class=linktabledata><td class=linktabledata>");
                            if(!dcrhtcp.equalsIgnoreCase(""))
                            {
                                if(rs.getString(1).equalsIgnoreCase(dcrhtcp))
                                    sb.append("<font color=red>Selected</font>");
                                else
                                {
                                    //sb.append("<input type=hidden name=maintcp"+rs.getString(1)+" value='U"+rs.getString(1)+"'>");
                                    sb.append("<input type=button class=buttoncolor name=U"+rs.getString(1)+"  Value=Select onClick=changeMainTCP('"+servPath+"','"+rs.getString(1)+"')>");
                                }
                            }
                            sb.append("</tr>");
                        }
                            rs.close();
                    }
                }
            }

            if(k==0)
                sb.append("<tr><td colspan=4 class=linktabledata><center><font face=verdana size=2 color=red>No Records Exists</font></center></td></tr>");

            sb.append("<input type=hidden name=drchshow value=\""+drchshow+"\">");
            sb.append("</table>");
        }
        catch(SQLException e)
        {
            Log.printStackTrace(e);
        }
        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("Connection Not Closing",e.toString());
            }
        }
        return sb.toString();
    }//areaDetailsMRLevel

	 /**This function returns the dcr area details for Upper Level.
        it is only for non field work area which has to be shown.
        @param drchshow The doctor/chemist flag. if "Y"
               then div tag with all neceessary will be show otherwise hidden.
        @param dcrno The DCR number for which dcr is to be entered.
     **/

    private String areaDetailsUPRLevel(String drchshow,String dcrno,String dbname)
    {
        StringBuffer sb = new StringBuffer();
        Connection con = null;
        Statement stmt = null;
        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();
            sb.append("<br><table width=\"97%\" class=\"linktableborder\">");
            int k = 0;
            if(!dcrno.equalsIgnoreCase(""))
            {
                ResultSet rs = null;
                sb.append("<tr><td valign=top>");
                boolean chk = false;

                //**********************************************************
                String wrkflag="";

                ResultSet rsdcrno=con.createStatement().executeQuery("select wrkflg from dcrh where dcrno='"+dcrno+"' ");
                if(rsdcrno.next())
                {
                   wrkflag=rsdcrno.getString(1);
                }
                //**********************************************************
                String nonwrkarea="";

                if(wrkflag.equalsIgnoreCase("01")||wrkflag.equalsIgnoreCase("1"))
                {
                   nonwrkarea = "SELECT DCRH.TCPID, IF(ISNULL(TCPHED.TOWN),'',TCPHED.TOWN) AS TOWN," +
                        " IF(ISNULL(WRKMST.ALLOWMULTIPLEAREA),'',WRKMST.ALLOWMULTIPLEAREA) AS ALLOWMULTIPLEAREA " +
                        " FROM DCRH LEFT JOIN TCPHED ON DCRH.NETID=TCPHED.NETID AND TCPHED.TCPID=DCRH.TCPID" +
                        " LEFT JOIN WRKMST ON TCPHED.WTYPE = WRKMST.WRKFLGCD  "+
                        " WHERE DCRNO=\""+dcrno+"\" AND DCRH.WRKFLG<>'01' ";
                }
                else
                {
                   nonwrkarea = "SELECT DCRH.TCPID, IF(ISNULL(TCPHED.TOWN),'',TCPHED.TOWN) AS TOWN," +
                        " IF(ISNULL(WRKMST.ALLOWMULTIPLEAREA),'',WRKMST.ALLOWMULTIPLEAREA) AS ALLOWMULTIPLEAREA " +
                        " FROM DCRH LEFT JOIN TCPHED ON DCRH.NETID=TCPHED.NETID AND TCPHED.TCPID=DCRH.TCPID" +
                        " LEFT JOIN WRKMST ON TCPHED.WTYPE = WRKMST.WRKFLGCD  "+
                        " WHERE DCRNO=\""+dcrno+"\" ";
                }
                //Log.log("501 : "+nonwrkarea);
                rs = stmt.executeQuery(nonwrkarea);

                if(rs.next())
                {
                    if(rs.getString(3).equalsIgnoreCase("N"))
                        drchshow = "N";

                    sb.append("<table class=linktableborder width=100%><tr><td class=linktablehead>Non Field Working<td class=linktablehead>");
                    chk = true;
                    if(!rs.getString(1).equalsIgnoreCase("000"))
                    {
                        k++;
                        sb.append("<tr><td class=linktabledata><font face=verdana color=black>"+rs.getString(1).trim()+"</font>\t<font face=verdana color=red>"+rs.getString(2).trim()+"</font>");
                        sb.append("<td class=linktabledata><input type=button name=nonworkarea class=buttoncolor value='Delete' onClick=deleteConfirmAR('A"+rs.getString(1)+"','"+servPath+"')></center></font>");
                        sb.append("</tr>");
                    }
                    sb.append("</table>");
                }
                sb.append("</td><td>");
               // if(chk==true)
              //  {
                    sb.append("<table class=linktableborder width=100%><tr><td class=linktablehead>Field Working<td class=linktablehead>");
                    rs = stmt.executeQuery("SELECT DISTINCT TCPHED.TOWN,NETMST.HNAME "+
                                "FROM DCRD "+
                                "LEFT JOIN TCPHED ON DCRD.WNETID=TCPHED.NETID AND TCPHED.TCPID=DCRD.TCPID "+
                                "LEFT JOIN NETMST ON NETMST.NETID = DCRD.WNETID "+
                                "WHERE DCRD.DCRNO=\""+dcrno+"\" ");
                    while(rs.next())
                    {
                        k++;
                        sb.append("<tr>");
                        sb.append("<td class=linktabledata><font face=verdana color=black>"+rs.getString(1).trim()+"</font>\t<td class=linktabledata><font face=verdana color=red>"+rs.getString(2).trim()+"</font>");
                        sb.append("</tr>");
                    }
                        rs.close();

                    sb.append("</table>");
                //}
                sb.append("</td></tr>");
            }
            if(k==0)
                sb.append("<tr><td colspan=4 class=linktabledata><center><font face=verdana size=2 color=red>No Records Exists</font></center></td></tr>");

            sb.append("<input type=hidden name=drchshow value='"+drchshow+"'>");
            sb.append("</table>");
        }
        catch(SQLException e)
        {
            Log.log(e.toString(),"Error");
        }

        finally
        {
           try
           {
               if(stmt!=null)
                    stmt.close();
               if(con!=null)
                    con.close();
           }catch(Exception e)
           {
               Log.log("Exception is not Closing ",e.toString());
           }
        }
            return sb.toString();
    }//areaDetailUPRLevel closes

    private String conflictNetids(String netid,String hqname,String dcrdate,String dcrhnetid,String dcrhhqname,String empcode)
    {
        StringBuffer sb = new StringBuffer();

        try
        {
            sb.append("\n<br><br><center><h2><u>Current HQ and DCR Entered HQ for this Date is not matching. </u></h2>");
            sb.append("<table class=linktableborder><tr><td class=linktablehead><b>NETID<td class=linktablehead><b>DCR NETID<td class=linktablehead><b>HQ NAME<td class=linktablehead><b>DCR HQ NAME<td class=linktablehead><b>DCR DATE");
            sb.append("<tr><td class=linktabledata><b>"+netid+"<td class=linktabledata><b>"+dcrhnetid+"<td class=linktabledata><b>"+hqname+"<td class=linktabledata><b>"+dcrhhqname+"<td class=linktabledata><b>"+dcrdate+"");
            sb.append("</table>");
            sb.append("<font size=2>Do you wish to Delete The Records exists with DCR.</font>&nbsp");
            sb.append("<a href="+servPath+"/eis.dataentry.DeleteDCREntry?mode=dcrscreen&dcrhnetid="+dcrhnetid+"&empcode="+empcode+"&dcrdate="+dcrdate+"> DELETE</a>");
            sb.append("<br><font size=2 color=red>If your current HQ is not matching, please Contact System Administrator to change the HQ</font>");
            sb.append("\n<br><br><input type=button value=Close onClick=window.close()>");
        }
        catch(Exception e)
        {
            Log.log(e.toString(),"Error");
        }
            return sb.toString();
    }

    private String[] getDCRHData(String empcode,String dcrdate,String dbname)
    {
        String retString[] = {"","","","","",""} ;
        Connection con = null;
        Statement stmt = null;
        try
        {
            Calendar c = Calendar.getInstance();
            retString[0] = c.get(c.YEAR)+"-"+(c.get(c.MONTH)+1)+"-"+c.get(c.DATE);


            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT RCPTDATE,ALLOW,FARE,DEDUCTION,DCRNO,TCPID " +
                                             "FROM DCRH WHERE EMP=\""+empcode+"\" AND DCRDATE=\""+dcrdate+"\" ");
            if(rs.next())
            {
                if(!rs.getString(1).equalsIgnoreCase("0000-00-00"))
                    retString[0] = rs.getString(1);

                retString[1] = rs.getString(2);
                retString[2] = rs.getString(3);
                retString[3] = rs.getString(4);
                retString[4] = rs.getString(5);
                retString[5] = rs.getString(6);
            }
                rs.close();
        }
        catch(Exception e)
        {
              Log.printStackTrace(e);
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }catch(Exception e)
            {
                Log.log("Exception",e.toString());
            }
        }
            return retString;
    }

   /* private String getWWithList(Vector wwithdca,Vector wwith,int option,String dcrno,String dcrdate,String empcode,String dcrnetidjoindate,String netid,int emplvl,String dbname)
    {
        StringBuffer sb = new StringBuffer();

        try
        {
            String wwithname="wwith";
            if(option==1)
                wwithname+="d";
            else if(option==2)
                wwithname+="c";
            else
                wwithname+="a";
            sb.append("<tr><td><input type=checkbox checked id=wwith name="+wwithname+"  size=4  class=checkbox onClick=javascript:setjnwrk('"+wwithname+"') >Independent");
             //sb.append("<tr><td><input type=checkbox checked id=wwith name=wwith  size=4  class=checkbox onClick=setjnwrk() >Independent");
            //sb.append("<tr><td><input type=button class=buttoncolor value='JointWorking' onClick=addjointworking('"+servPath+"','"+wwithdca+"','"+dcrno+"')>");
            sb.append("<tr><td><input name=jnwrk type=button disabled=true class=buttoncolor value='JointWorking' onClick=javascript:addjointworking('"+servPath+"','"+dcrdate+"','"+empcode+"','"+dcrnetidjoindate+"','"+netid+"','"+emplvl+"','"+dbname+"','"+dcrno+"')>");
            String selected ;
            Vector row ;Vector row1;
            String temp="";
            /*
            sb.append("<select>");
            for(int i=0; i < wwithdca.size(); i++)
            {
                row = (Vector)wwithdca.elementAt(i);
                selected = "";
                if(wwith.contains(row.elementAt(0).toString()))        //row.elementAt(0).toString().equalsIgnoreCase(wwith)
                    selected = "selected";
                    sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+"\"><input type=checkbox name=chk\""+row.elementAt(0).toString()+"\">"+row.elementAt(1).toString()+"</option>");
            }
            sb.append("</select>");
            */
           /* for(int i=0; i < ecode.size(); i++)
            {
                row1 = (Vector)ecode.
                sb.append("<input type= name value=\""+row1.elementAt(1).toString()+"\">");
            }
             sb.append("<input type=hidden name=tIndependent value=\"Independent\">");
             */
       // }
      //  catch(Exception e)
       // {
      //      Log.printStackTrace(e);
      // }
     //   return sb.toString();
   // }
    //getwwithlistbox(closesold)

    private String getWWithListBox(Vector wwithdca,Vector wwith,int option,String flag)
       {
           //Log.log("WWITH"+wwithdca.size());
           StringBuffer sb = new StringBuffer();
           try
           {
               String wwithname ="wwith";

               if(option==1)
                   wwithname+="d";
               else if(option==2)
                   wwithname+="c";
               else
                   wwithname+="a";

               int c=wwith.size();
                   c=4;
               if(c>4)
                   c=4;

                if(wwith.size()==0)
                {
                  sb.append("<tr><td><select disabled id=wwith size=1 name="+wwithname+" multiple=\"multiple\"   class=listbox onChange=wwithChange('wwith','"+option+"')><option selected value=\"Indep\">INDEPENDENT</option>");
                  sb.append("</select>");
                }
                else
                {
                  sb.append("<tr><td><select disabled size=\""+c+"\" id=wwith name="+wwithname+" multiple=\"multiple\" class=listbox onChange=wwithChange('wwith','"+option+"')>");
                  String selected ;
                  Vector row ;Vector row1;
                  // if(wwith.size()==0)
                  // sb.append("<option selected value=\"Independent\">INDEPENDENT</option>");

         /*      for(int i=0; i < wwithdca.size(); i++)
                  {
                        row = (Vector)wwithdca.elementAt(i);
                        selected = "";
                        if(wwith.contains(row.elementAt(0).toString()))        //row.elementAt(0).toString().equalsIgnoreCase(wwith)
                        {
                           selected = "selected";
                           sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+"\">"+row.elementAt(1).toString()+"</option>");
                         }
                   }
           */

                for(int i=0; i < wwithdca.size(); i++)
                {
                   row = (Vector)wwithdca.elementAt(i);
                   selected = "";
                 // if((wwith.size()>0) && (wwith.size()<5))
                  //{
                   if(wwith.contains(row.elementAt(0).toString()))        //row.elementAt(0).toString().equalsIgnoreCase(wwith)
                   {
                       selected = "selected";
                       sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+"\">"+row.elementAt(1).toString()+"</option>");
                   }
                  // }
                }
                   sb.append("</select>");

                for(int i=0; i < wwithdca.size(); i++)
                {
                   row1 = (Vector)wwithdca.elementAt(i);
                   sb.append("<input type=hidden name=t"+row1.elementAt(0).toString()+" value=\""+row1.elementAt(1).toString()+"\">");
                }
                   sb.append("<input type=hidden name=tIndependent value=\"Independent\">");
              }
           }
           catch(Exception e)
           {
                 Log.printStackTrace(e);
           }
           return sb.toString();
       }

       private String getWWithListBox(Vector wwithdca,Vector wwith,int option)
       {
           StringBuffer sb = new StringBuffer();
           try
           {
               String wwithname = "wwith";

               if(option==1)
                   wwithname+="d";
               else if(option==2)
                   wwithname+="c";
               else
                   wwithname+="a";

               if(wwithdca.size()==0)
                  sb.append("<tr><td><select id=wwith size=1 name="+wwithname+" multiple=\"multiple\" class=listbox onChange=wwithChange('wwith','"+option+"') >" +
                          "<option selected value=Independent>INDEPENDENT</option>");
               else
               {
                  sb.append("<tr><td><select disabled=true id=wwith name="+wwithname+" multiple=\"multiple\" class=listbox onChange=wwithChange('wwith','"+option+"')>");
               }

               String selected ;
               Vector row ;Vector row1;
               for(int i=0; i < wwithdca.size(); i++)
               {
                   row = (Vector)wwithdca.elementAt(i);
                   selected = "";
                 //  if(wwith.contains(row.elementAt(0).toString()))        //row.elementAt(0).toString().equalsIgnoreCase(wwith)
                //    {
                       selected = "selected";
                       sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+"\">"+row.elementAt(1).toString()+"</option>");
                //   }
               }

                sb.append("</select>");
                for(int i=0; i < wwithdca.size(); i++)
                {
                   row1 = (Vector)wwithdca.elementAt(i);
                   sb.append("<input type=hidden name=t"+row1.elementAt(0).toString()+" value=\""+row1.elementAt(1).toString()+"\">");
                }

                 sb.append("<input type=hidden 0name=tIndependent value=\"Independent\">");
           }
           catch(Exception e)
           {
                 Log.printStackTrace(e);
           }
           return sb.toString();
       }

    private Vector getWWithVector(String yr,String mon,String netid,String empcode,String dcrnetidjoindate,int emplvl,String dbname)
    {
        Vector retVector = new Vector();
        String sql ="";
        Connection con = null;
        Statement stmt = null;
        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();
            ResultSet rs = null;
            if(emplvl==1)
            {
                sql+=("SELECT LEVEL2,LEVEL3,LEVEL4,LEVEL5 FROM NETTRANS WHERE YR=\""+yr+"\" AND MTH=\""+mon+"\" AND NETID=\""+netid+"\" AND LEVEL1=\""+empcode+"\" AND JOINDATE=\""+dcrnetidjoindate+"\" ");//050604
                rs = stmt.executeQuery(sql);
                if(rs.next())
                {
                    sql = "SELECT DISTINCT ECODE,ENAME FROM EMPMST WHERE ECODE=\""+rs.getString(1)+"\" " +
                          "UNION SELECT DISTINCT ECODE,ENAME FROM EMPMST WHERE ECODE=\""+rs.getString(2)+"\" " +
                          "UNION SELECT DISTINCT ECODE,ENAME FROM EMPMST WHERE ECODE=\""+rs.getString(3)+"\" " +
                          "UNION SELECT DISTINCT ECODE,ENAME FROM EMPMST WHERE ECODE=\""+rs.getString(4)+"\" " +
                          "UNION SELECT DISTINCT ECODE,ENAME FROM EMPMST WHERE ETYPE=9 and resdate =\"0000-00-00\" "; // This stmt has been added for joint working with product manager
                }
            }
            else
            {
                sql+="SELECT DISTINCT LEVEL1,ENAME,\"1\" AS LEVEL FROM NETTRANS "+
                             "LEFT JOIN EMPMST ON EMPMST.ECODE = NETTRANS.LEVEL1 "+
                             "WHERE YR=\""+yr+"\" AND MTH=\""+mon+"\" AND NETTRANS.LEVEL"+emplvl+"='"+empcode+"' "+
                             "AND LEVEL1<>'"+empcode+"' ";

                for(int i=2;i<6;i++)
                {
                    if(emplvl!=i)
                    {
                        sql+=" UNION SELECT DISTINCT LEVEL"+i+",ENAME,\"2\" AS LEVEL FROM NETTRANS "+
                              " LEFT JOIN EMPMST ON EMPMST.ECODE=NETTRANS.LEVEL"+i+" "+
                              " WHERE YR=\""+yr+"\" AND MTH=\""+mon+"\" AND NETID=\""+netid+"\" AND NETTRANS.JOINDATE=\""+dcrnetidjoindate+"\" "+
                              " AND LEVEL"+i+"<>\""+empcode+"\" ";
                    }
                }
                sql+= " ORDER BY LEVEL";
            }


            rs = stmt.executeQuery(sql);
            Vector tempVector ;
            tempVector = new Vector();
            tempVector.add("");

            //tempVector.add("");                vvvvvvvvvvvvvvvvvvvvvvvvv
            //retVector.add(tempVector);

            while(rs.next())
            {
                tempVector = new Vector();
                String ecode="";
                if(rs.getString(1)!=null) ecode=rs.getString(1);
                tempVector.add(ecode);
                String ename="";
                if(rs.getString(2)!=null) ename=rs.getString(2);
                tempVector.add(ename);
                retVector.add(tempVector);
            }
                rs.close();
        }
        catch(Exception e)
        {
              Log.printStackTrace(e);
        }
        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("Exception",e.toString());
            }
        }
            return retVector;
    }


// -- Changed due to page NOT loading properly Changed by glenn

    private Vector getWNetidVector(String yr,String mon,String empcode,int emplvl,String dbname)
    {
        Connection con = null;
        Statement stmt = null;

        Vector retVector = new Vector();
        String sql = "SELECT DISTINCT NETTRANS.NETID,NETMST.HNAME,EMPMST.ENAME " +
                "FROM NETTRANS " +
                "LEFT JOIN NETMST ON NETMST.NETID=NETTRANS.NETID " +
                "LEFT JOIN EMPMST ON EMPMST.ECODE=NETTRANS.LEVEL1 " +
                "WHERE NETTRANS.YR=\""+yr+"\" AND NETTRANS.MTH=\""+mon+"\" " +
                "AND NETTRANS.LEVEL"+emplvl+"='"+empcode+"' AND NETMST.LEVEL='1'";
        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            Vector tempVector ;
            while(rs.next())
            {
                tempVector = new Vector();
    			String netid=""; if(rs.getString(1)!=null) netid=rs.getString(1); tempVector.add(netid);
                String hname=""; if(rs.getString(2)!=null) hname=rs.getString(2); tempVector.add(hname);
                String ename=""; if(rs.getString(3)!=null) ename=rs.getString(3); tempVector.add(ename);
				retVector.add(tempVector);
            }
                rs.close();
        }

        catch(Exception e)
        {
              Log.log("getWNetidVector",e.toString());
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }catch(Exception e)
            {
                Log.log("getWNetidVector",e.toString());
            }
         }
            return retVector;
    }

    private String getWNetidListBox(Vector wnetiddc,String wnetid,int option)
    {
        StringBuffer sb = new StringBuffer();
        try
        {
            String wnetidname = "wnetid";
            if(option==1)
            {
                wnetidname+="d";
              // sb.append("<td><select name="+wnetidname+" class=listbox onChange=doformatd('"+servPath+"')>");//onChange=wwithChange('wwith',"+option+")
                sb.append("<td><select name="+wnetidname+" class=listbox onChange=doformatd('"+servPath+"')>");//onChange=wwithChange('wwith',"+option+")
            }
            else
            {
                wnetidname+="c";
                sb.append("<td><select name="+wnetidname+" class=listbox onChange=doformatc('"+servPath+"')>");//onChange=wwithChange('wwith',"+option+")
            }

            String selected ="";

            Vector row ;

            for(int i=0;i<wnetiddc.size();i++)
            {
                row = (Vector)wnetiddc.elementAt(i);
                selected = "";
                if(wnetid.equalsIgnoreCase("") && i==0)
                    selected = "selected";
                if(row.elementAt(0).toString().equalsIgnoreCase(wnetid))
                    selected = "selected";
                sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+"\">"+row.elementAt(1).toString()+" / "+row.elementAt(2).toString()+"</option>");
            }
                sb.append("</select>");
        }
        catch(Exception e)
        {
              Log.printStackTrace(e);
        }
        return sb.toString();
    }


// Connection Variable is Local due to refresh problems // changed by glenn
    private Vector getAreaDCVector(String wnetid,String dbname,String dcrdate)
    {
        Connection con = null;
        Statement stmt = null;
        Vector retVector = new Vector();
       /* String sql = "SELECT TCPID, TOWN, TOWNID, WTYPE FROM TCPHED, WRKMST " +
                "WHERE TCPHED.WTYPE = WRKMST.WRKFLGCD AND TCPHED.NETID=\""+wnetid+"\" " +
                "AND TCPHED.DELDATE = '0000-00-00' AND TCPHED.APRVCODE<>'' " +
                "AND WRKMST.DRCHCALLSFLG = 'Y' ORDER BY TOWN ";
       */

          String sql = "SELECT TCPID, TOWN, TOWNID,WTYPE FROM TCPHED, WRKMST " +
                " WHERE TCPHED.WTYPE = WRKMST.WRKFLGCD AND TCPHED.NETID=\""+wnetid+"\" " +
              //  "AND TCPHED.DELDATE = '0000-00-00' AND TCPHED.APRVCODE<>'' " +
              //  " AND (TCPHED.DELDATE='0000-00-00' OR TCPHED.DELDATE>='"+wrkdate+"') AND TCPHED.APRVCODE<>'' "+
                " AND (TCPHED.DELDATE='0000-00-00' OR TCPHED.DELDATE>='"+dcrdate+"') AND TCPHED.APRVCODE<>'' "+
                " AND WRKMST.DRCHCALLSFLG = 'Y' ORDER BY TOWN ";
        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            Vector tempVector ;

            while(rs.next())
            {
                tempVector = new Vector();
    			tempVector.add(rs.getString(1));
                tempVector.add(rs.getString(2));
                tempVector.add(rs.getString(3));
                tempVector.add(rs.getString(4));
				retVector.add(tempVector);
            }
                rs.close();
        }
        catch(Exception e)
        {
              Log.log("Error Is Here",e.toString());
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }catch(Exception e)
            {
                Log.log("Exception in method getAreaDcVector",e.toString());
            }
        }
        return retVector;
    }

    private String getareaListBox(Vector areadc,String tcpid,int option,int emplvl)
    {
	    StringBuffer sb = new StringBuffer();
         try
         {
            Vector row;
            String name="tcpidd";
            if(option==2)
                name="tcpidc";
            String selected = "";
            if(tcpid.equalsIgnoreCase(""))
                selected = "selected";

    	    sb.append("<td><select name="+name+" class=listbox onChange=wwithChange('"+name+"',0)>");
            if(tcpid.equalsIgnoreCase(""))
               sb.append("<option "+selected+" value=\"Select Schedule\">Select Schedule</option>");
            else
               sb.append("<option value=\"Select Schedule\">Select Schedule</option>");

            for(int i=0;i<areadc.size();i++)
            {
                row =(Vector)areadc.elementAt(i);
                selected = "";
                if(emplvl==1)
                {
                        if(row.elementAt(0).toString().equalsIgnoreCase(tcpid))
                        {
                             selected = "selected";
                             sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+":"+row.elementAt(3).toString()+"\">"+row.elementAt(1).toString()+" / "+row.elementAt(2).toString()+"</option>");
                        }
                }
                else
                {
                       if(row.elementAt(0).toString().equalsIgnoreCase(tcpid))
                       {
                           selected = "selected";
                           sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+":"+row.elementAt(3).toString()+"\">"+row.elementAt(1).toString()+" / "+row.elementAt(2).toString()+"</option>");
                       }
                       else
                       {
                           selected = "";
                           sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+":"+row.elementAt(3).toString()+"\">"+row.elementAt(1).toString()+" / "+row.elementAt(2).toString()+"</option>");
                       }
                }
            }
            sb.append("</select>");
	    }//try closes
    	catch(Exception e)
	    {
		    Log.printStackTrace(e);
    	}//catch closes
	    return sb.toString();
    }//showselect closes

    private String getnonfieldworkareaListBox(Vector areaa,String tcpid)
    {
	    StringBuffer sb=new StringBuffer();
	    try
	    {
            Vector row ;
            String selected = "";

    	    sb.append("<td> <select name=corearea class=listbox>");
            sb.append("<option  value=\"\">Select</option>");
            for(int i=0;i<areaa.size();i++)
            {
                row = (Vector)areaa.elementAt(i);
                if(row.elementAt(0).toString().equalsIgnoreCase(tcpid))
                    selected = "selected";
                sb.append("<option "+selected+" value=\""+row.elementAt(0).toString()+":"+row.elementAt(3).toString()+"\">"+row.elementAt(1).toString()+" / "+row.elementAt(2).toString()+"</option>");
            }
            sb.append("</select>");

	    }//try closes
    	catch(Exception e)
	    {
		    Log.printStackTrace(e);
    	}//catch closes
	    return sb.toString();
    }//showselect closes


    private Vector getAreaAVector(String netid,boolean drchexist,String dbname)
    {
        Vector retVector = new Vector();

        String sql = "SELECT TCPID, TOWN, TOWNID, WTYPE FROM TCPHED, WRKMST " +
                "WHERE TCPHED.WTYPE = WRKMST.WRKFLGCD AND NETID = \""+netid+"\" " +
                "AND (TCPHED.DELDATE='0000-00-00' OR TCPHED.DELDATE>'"+wrkdate+"') AND DRCHCALLSFLG = 'N' AND TCPHED.APRVCODE<>'' ";
        if(drchexist)
            sql+= " AND ALLOWMULTIPLEAREA = 'Y'	";

        sql+=" ORDER BY WTYPE,TOWN ";

        Connection con = null;
        Statement stmt = null;

        try
        {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(sql);

            Vector tempVector ;

            while(rs.next())
            {
                tempVector = new Vector();
    			tempVector.add(rs.getString(1));
                tempVector.add(rs.getString(2));
                tempVector.add(rs.getString(3));
                tempVector.add(rs.getString(4));
				retVector.add(tempVector);
            }
                rs.close();
        }
        catch(Exception e)
        {
              Log.log(e.toString(),"Error");
        }
        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }catch(Exception e)
            {
                Log.log("Exception",e.toString());
            }
        }
            return retVector;
    }

    private void getSRFCMTP(String dcrdate,String netid,String dbname,String emp)
    {
     Vector srfc=new Vector();
     String max="";String rcptdate="";String tcpid="";
     Connection con=null;
     Statement stmt=null;
        try{
            Calendar c = Calendar.getInstance();
            rcptdate = c.get(c.YEAR)+"-"+(c.get(c.MONTH)+1)+"-"+c.get(c.DATE);


            con = ConnectionHandler.getConnection(dbname);

            stmt=con.createStatement();

         ResultSet rs1=stmt.executeQuery("select if(isnull(max(dcrno)+1),'00000001',max(dcrno)+1) as mm from dcrh ");
            if(rs1.next())
              max=rs1.getString(1);

            String maxo="";
            for(int m=0;m<8-max.length();m++)
            maxo+="0";
            max=maxo+max;
            ResultSet rs2=stmt.executeQuery("select tcpid from mtp where netid='"+netid+"' and wdate='"+dcrdate+"' and aprvcode<>''");
            if(rs2.next())
              tcpid=rs2.getString(1);

         stmt.executeUpdate("insert into dcrh(dcrno,netid,emp,dcrdate,rcptdate,acdate,wrkflg,entdate,tcpid,user) values" +
                            "('"+max+"','"+netid+"','"+emp+"','"+dcrdate+"','"+rcptdate+"','0000-00-00','01','"+rcptdate+"','"+tcpid+"','"+emp+"')");

         ResultSet rs3=stmt.executeQuery("select cntcd from srfc where netid='"+netid+"' and tcpid='"+tcpid+"' and deldate='0000-00-00' ");
           int i=1;
         while(rs3.next())
         {
             String ii=Integer.toString(i);
             if(ii.length()==1)
             ii="00"+ii;
             if(ii.length()==2)
             ii="0"+ii;

             stmt.executeUpdate("insert into dcrd(dcrno,wwith,wnetid,tcpid,custflg,cntcd,serial,DrConverted,DrRemark,wwith2,wwith3,wwith4,status) values " +
                     "('"+max+"','','"+netid+"','"+tcpid+"','D','"+rs3.getString(1)+"','"+ii+"','','','','','','C')");
             i=i+1;
         }

            /*
         ResultSet rs=stmt.executeQuery("select D.DRCD,D.DRNAME,D.CNTCD,'',S.TCPID,'','','','' from SRFC S,MTP M,DOCHED D WHERE S.NETID=M.NETID AND S.NETID='"+netid+"' AND M.WDate='"+dcrdate+"' AND S.TCPID=M.TCPID AND M.APRVCODE<>'' AND S.CNTCD=D.CNTCD ");

         while(rs.next())
         {
             Vector tmp=new Vector();
             tmp.add(rs.getString(1));
             tmp.add(rs.getString(2));
             tmp.add(rs.getString(3));
             tmp.add(rs.getString(4));
             tmp.add(rs.getString(5));
             tmp.add(rs.getString(6));
             tmp.add(rs.getString(7));
             tmp.add(rs.getString(8));
             tmp.add(rs.getString(9));
             srfc.add(tmp);
         }                 */
        }catch(Exception e)

        {Log.printStackTrace(e);}
        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("MTP SRFC",e.toString());
            }
        }
    }

    private Vector getDoctorDetailsVector(String dcrno,String dbname,String dcrdate)
    {
        Connection con = null;
        Statement stmt = null;
        Vector retVector = new Vector();
        if(!dcrno.equalsIgnoreCase(""))
        {
            try
            {

                con = ConnectionHandler.getConnection(dbname);

                Vector tempVector;
                stmt = con.createStatement();
                String sql = "";
                String s="SHOW FIELDS FROM DCRD like 'WWITH2'";
                ResultSet rs1=stmt.executeQuery( s );
                if(rs1.next())
                 ;
            else
            {
             sql="ALTER TABLE DCRD ADD COLUMN WWITH2 VARCHAR(5) DEFAULT \"\" ";
                  stmt.executeUpdate(sql);  }
             s=" SHOW FIELDS FROM DCRD like 'WWITH3'";
                         ResultSet rs2=stmt.executeQuery( s );
                        if(rs2.next())
                             ;
                        else
                        {  sql="ALTER TABLE DCRD ADD COLUMN WWITH3 VARCHAR(5) DEFAULT \"\" ";
                              stmt.executeUpdate(sql);  }

            s=" SHOW FIELDS FROM DCRD like 'WWITH4'";
                        ResultSet rs3=stmt.executeQuery( s );
                        if(rs3.next())
                             ;
                        else
                        {
                            sql="ALTER TABLE DCRD ADD COLUMN WWITH4 VARCHAR(5) DEFAULT \"\" ";
                            stmt.executeUpdate(sql);
                        }
             s="SHOW FIELDS FROM DCRD like 'status' ";
             ResultSet rss=stmt.executeQuery( s );
             if(rss.next())
                 ;
             else
             {
                sql=" ALTER TABLE DCRD ADD COLUMN STATUS CHAR(2) DEFAULT \"\" ";
                stmt.executeUpdate(sql);
             }
                    String curryr = dcrdate.substring(0,4);
                    String currmth = dcrdate.substring(5,7);
                    if(currmth.substring(1,2).equalsIgnoreCase("-"))
                        currmth="0"+dcrdate.substring(5,6);

                    String dcrdateyrmth = curryr+currmth;
                con.createStatement().executeUpdate("drop table if exists TEMPDCRDR ");

                sql = "CREATE TEMPORARY TABLE TEMPDCRDR SELECT IF(DOCHED.DRCD IS NULL,'',DOCHED.DRCD)," +
                        " IF(DOCHED.DRNAME IS NULL,'NON  " + pname001 + " ',DOCHED.DRNAME)," +
                        " DCRD.SERIAL,DCRD.WWITH,DCRD.WNETID,DCRD.TCPID,DCRD.WWITH2,DCRD.WWITH3,DCRD.WWITH4 " +
                        " FROM DCRD LEFT JOIN DOCHED ON ( DOCHED.CNTCD=DCRD.CNTCD AND DCRD.WNETID=DOCHED.NETID " +
                        " AND DCRD.DCRNO='"+dcrno+"' ) " +
                        " WHERE DCRD.DCRNO=\""+dcrno+"\" AND CUSTFLG = 'D'  " +
                        " AND (DELDATE='0000-00-00' OR CONCAT(SUBSTRING(DELDATE,1,4),SUBSTRING(DELDATE,6,2))>='"+dcrdateyrmth+"') " +
                        " AND DCRD.CNTCD<>'000ND1' "+
                        " GROUP BY DCRD.WNETID,DCRD.CNTCD ORDER BY SERIAL ";
                con.createStatement().executeUpdate(sql);

                sql = " insert into TEMPDCRDR SELECT IF(DOCHED.DRCD IS NULL,'',DOCHED.DRCD)," +
                        "IF(DOCHED.DRNAME IS NULL,'NON  " + pname001 + " ',DOCHED.DRNAME)," +
                        "DCRD.SERIAL,DCRD.WWITH,DCRD.WNETID,DCRD.TCPID,DCRD.WWITH2,DCRD.WWITH3,DCRD.WWITH4 " +
                        " FROM DCRD LEFT JOIN DOCHED ON ( DOCHED.CNTCD=DCRD.CNTCD AND DCRD.WNETID=DOCHED.NETID " +
                        " AND DCRD.DCRNO='"+dcrno+"' ) " +
                        " WHERE DCRD.DCRNO=\""+dcrno+"\" AND CUSTFLG = 'D'  " +
                        " AND DCRD.CNTCD='000ND1' "+
                        " ORDER BY SERIAL ";
                 con.createStatement().executeUpdate(sql);

                 sql ="select * from TEMPDCRDR ";

                //stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                while(rs.next())
                {
                    tempVector = new Vector();
                    tempVector.add(rs.getString(1));
                    tempVector.add(rs.getString(2));
                    tempVector.add(rs.getString(3));
                    tempVector.add(rs.getString(4));
                    tempVector.add(rs.getString(5));
                    tempVector.add(rs.getString(6));
                    String ww1="";
                    if(rs.getString(7)!=null)
                        ww1=rs.getString(7);
                    String ww2="";
                    if(rs.getString(8)!=null)
                        ww2=rs.getString(8);
                    String ww3="";
                    if(rs.getString(9)!=null)
                        ww3=rs.getString(9);
                    tempVector.add(ww1);
                    tempVector.add(ww2);
                    tempVector.add(ww3);
                    retVector.add(tempVector);
                }
            }
            catch(SQLException e)
            {
                Log.log(e.toString(),"Error");
            }
            finally
            {
                try
                {
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                }catch(Exception e)
                {
                    Log.log("Exception",e.toString());
                }
            }
        }
                return retVector;
    }

    private Vector getChemistDetailsVector(String dcrno,String dbname)
    {
        Vector retVector = new Vector();
        Connection con = null;
        Statement stmt = null;

        if(!dcrno.equalsIgnoreCase(""))
        {
            try
            {
                Vector tempVector;

                String sql = "SELECT IF(STOCHED.STCD IS NULL,'',STOCHED.STCD)," +
                        "IF(STOCHED.STNAME IS NULL,'NON  " + pname001 + "',STOCHED.STNAME)," +
                        "DCRD.SERIAL,DCRD.WWITH,DCRD.WNETID,DCRD.TCPID,DCRD.WWITH2,DCRD.WWITH3,DCRD.WWITH4 " +
                        "FROM DCRD LEFT JOIN STOCHED ON STOCHED.CNTCD=DCRD.CNTCD " +
                        "WHERE DCRD.DCRNO=\""+dcrno+"\" AND CUSTFLG = 'C' ORDER BY SERIAL ";


                con = ConnectionHandler.getConnection(dbname);

                stmt = con.createStatement();

                ResultSet rs = stmt.executeQuery(sql);
                while(rs.next())
                {
                    tempVector = new Vector();
                    tempVector.add(rs.getString(1));
                    tempVector.add(rs.getString(2));
                    tempVector.add(rs.getString(3));
                    tempVector.add(rs.getString(4));
                    tempVector.add(rs.getString(5));
                    tempVector.add(rs.getString(6));
                    String ww1="";if(rs.getString(7)!=null) ww1=rs.getString(7);
                    String ww2="";if(rs.getString(8)!=null) ww2=rs.getString(8);
                    String ww3="";if(rs.getString(9)!=null) ww3=rs.getString(9);
                    tempVector.add(ww1);
                    tempVector.add(ww2);
                    tempVector.add(ww3);
                    retVector.add(tempVector);
                }
            }
            catch(SQLException e)
            {
                Log.log(e.toString(),"Error");
            }
            finally
            {
                try
                {
                   if(stmt!=null)
                       stmt.close();
                   if(con!=null)
                       con.close();
                }catch(Exception e)
                {
                    Log.log("Exceptions",e.toString());
                }
            }
        }
                return retVector;
    }

    private Vector getExpenseDetailsVector(String dcrno,int emplvl,String dbname)
    {
         Connection con = null;
         Statement stmt = null;

         Vector retVector = new Vector();

        if(!dcrno.equalsIgnoreCase(""))
        {
            try
            {

                con = ConnectionHandler.getConnection(dbname);


                stmt = con.createStatement();

                int type=emplvl;

                if(emplvl > 1)
                    type=2;

                Vector tempVector;
                String sql = "SELECT EXPMST.EDESC,DCRE.AMOUNT,EXPMST.EXPCD FROM DCRE,EXPMST " +
                        "WHERE DCRE.DCRNO=\""+dcrno+"\"  AND EXPMST.LTYPE='"+type+"' " +
                        "AND DCRE.EXPCD=EXPMST.EXPCD";

                ResultSet rs = stmt.executeQuery(sql);

                while(rs.next())
                {
                    tempVector = new Vector();
                    tempVector.add(rs.getString(1));
                    tempVector.add(rs.getString(2));
                    tempVector.add(rs.getString(3));
                    retVector.add(tempVector);
                }
                    stmt.close();
                    con.close();
            }
            catch(SQLException e)
            {
                Log.log(e.toString(),"Error");
            }

            finally
            {
                try
                {
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                }catch(Exception e)
                {
                    Log.log("Exception",e.toString());
                }
            }
        }
                return retVector;
    }

    private String getRemark(String dcrno,String dbname)
    {
        StringBuffer sb = new StringBuffer();

        Connection con = null;
        Statement stmt = null;

        try
        {

            con = ConnectionHandler.getConnection(dbname);


            stmt = con.createStatement();

            if(!dcrno.equalsIgnoreCase(""))
            {
                String sql =  "SELECT DREMARK FROM DCRR WHERE DCRNO=\""+dcrno+"\" ";

                ResultSet rs = stmt.executeQuery(sql);

				sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Remarks:<td width=70% valign=top halign=top><font face=verdana color=black>");

                if(rs.next())
                {
                    String remark=rs.getString(1);
                    if(remark==null)
                        remark="";
                    sb.append("<textarea rows=2 cols=60 name=remark class=textbox>"+remark+"</textarea></font>");
                }
                else
                {
                    sb.append("<textarea rows=2 cols=60 name=remark class=textbox></textarea></font>");
                }
                    rs.close();
            }
            else
                sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Remarks:<td width=70% valign=top halign=top><font face=verdana color=black><textarea rows=2 cols=50 name=remark class=textbox></textarea></font>");

        }
        catch(SQLException e)
        {
            Log.printStackTrace(e);
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }
            catch(Exception e)
            {
                Log.log("Exception in Get Remark",e.toString());
            }
        }
            return sb.toString();
    }

 /*   private String getReason(String dcrno,String dbname,String dcrdate,String netid)
    {
        StringBuffer sb = new StringBuffer();

        Connection con = null;
        Statement stmt = null;

        try
        {
            con = ConnectionHandler.getConnection(dbname);
            stmt = con.createStatement();
            ResultSet rs1=stmt.executeQuery("select d.tcpid from mtp m,dcrd d where m.netid='"+netid+"' and m.wdate='"+dcrdate+"' "+
                                            " and d.dcrno='"+dcrno+"' and m.tcpid<>d.tcpid group by d.tcpid");
            if(rs1.next())
            {
            if(!rs1.getString(1).equalsIgnoreCase(""))
            {
            if(!dcrno.equalsIgnoreCase(""))
            {
                String sql =  "SELECT DREASON FROM DCRR WHERE DCRNO=\""+dcrno+"\" ";

                ResultSet rs = stmt.executeQuery(sql);

                sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Reason :<td width=70% valign=top halign=top><font face=verdana color=black>");

                if(rs.next())
                {
                    String reason=rs.getString(1);if (reason==null) reason="";
                    sb.append("<textarea rows=2 cols=60 name=reason class=textbox>"+reason+"</textarea></font>");
                }
                else
                {
                    sb.append("<textarea rows=2 cols=60 name=reason class=textbox></textarea></font>");
                }
                    rs.close();
            }
            else
                sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Reason:<td width=70% valign=top halign=top><font face=verdana color=black><textarea rows=2 cols=50 name=reason class=textbox></textarea></font>");
            }
           }
           else
               sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Reason:<td width=70% valign=top halign=top><font face=verdana color=black><textarea rows=2 cols=50 name=reason class=textbox></textarea></font>");
        }
        catch(SQLException e)
        {
            Log.printStackTrace(e);
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("Exception in Get Reason",e.toString());
            }
        }
            return sb.toString();
    }
 */

    private String getReason(String dcrno,String dbname,String dcrdate,String netid)
     {
        StringBuffer sb = new StringBuffer();

        Connection con = null;
        Statement stmt = null;

        try
        {

            con = ConnectionHandler.getConnection(dbname);


            stmt = con.createStatement();

            if(!dcrno.equalsIgnoreCase(""))
            {
                String sql =  "SELECT DREASON FROM DCRR WHERE DCRNO=\""+dcrno+"\" ";

                ResultSet rs = stmt.executeQuery(sql);

				sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Reason:<td width=70% valign=top halign=top><font face=verdana color=black>");

                if(rs.next())
                {
                    String reason=rs.getString(1);
                    if(reason==null)
                        reason="";
                    sb.append("<textarea rows=2 cols=60 name=reason class=textbox>"+reason+"</textarea></font>");
                }
                else
                {
                    sb.append("<textarea rows=2 cols=60 name=reason class=textbox></textarea></font>");
                }
                    rs.close();
            }
            else
                sb.append("<tr><td width=10% valign=top halign=top><font face=verdana color=black>Reason:<td width=70% valign=top halign=top><font face=verdana color=black><textarea rows=2 cols=50 name=reason class=textbox></textarea></font>");
        }
        catch(SQLException e)
        {
            Log.printStackTrace(e);
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }
            catch(Exception e)
            {
                Log.log("Exception in Get Remark",e.toString());
            }
        }
            return sb.toString();
    }



    private String getDoctorDetails(Vector doctordetails,String serial,String dcrhtcpid,String dcrno,String dbname,Connection con,String empcode,Vector doctordetailsSRFC)
    {
        StringBuffer sb = new StringBuffer();

        String valexists="";
        Vector tempvector;
            /*
                for(int i=0;i<doctordetailsSRFC.size();i++)
                {
                Vector tmp=new Vector();
                tmp=(Vector)doctordetailsSRFC.elementAt(i);
                doctordetails.add(tmp);
                }
               Log.log(doctordetails.toString());
            */
        if(doctordetails.size()>0)
        {
            for(int i=0;i<doctordetails.size();)
            {
                tempvector = (Vector)doctordetails.elementAt(i);
                String drcd = tempvector.elementAt(0).toString();
                String drname = tempvector.elementAt(1).toString();
                String  drserial = tempvector.elementAt(2).toString();
                String check = "";

                if(drserial.equalsIgnoreCase(serial))
                    check= "checked";
                i++;

                if(i%4==1)
                    sb.append("<tr>");

                sb.append("<td width=25%><input type=radio name=drchd "+check+" value='DR"+drserial+"' onClick=javascript:DrRadio();>" +
                          "<font face=verdana color=red>"+drcd.trim()+"</font>\t<font face=verdana color=black>" +
                           "" +drname.trim()+"</a></font></td>");
                          // "<A title='"+getDoctorDetail(dcrno,drserial)+"'>"+drname.trim()+"</a></font></td>");
                         // "<A title='javascript:document.write(&lt;html&gt;<table><tr><td>aaNil</td></tr><tr><td>Nil</td></tr></table></html>)'>"+drname.trim()+"</a></font></td>");
            }
        }
        sb.append("<input type=hidden name=eled value='"+doctordetails.size()+"'>");

        InfoHelper ih = null;
        ih = new InfoHelper(con,empcode,dbname);

        if(doctordetails.size()==0)
            sb.append("<tr><td class=linktabledata><center><font face=verdana size=2 color=red>No Records Exists</font></center></td>");
        sb.append("</tr></table>");
        sb.append("<table  align=center>");
		sb.append("<tr><td><font class=tbltextmfs><input type=button name=vsttme class=buttoncolor value='Visit Time/CCR Calls/Work With' onClick=javascript:visitTime('"+servPath+"','"+dcrno+"')></font>");
        String valexists1 = getsysprmcount("dcrstatus",con);

        String remarks=remarkExists(dbname,valexists1,"dcrstatus","N");

        if(remarks.equalsIgnoreCase("Y"))

        sb.append( "<font class=tbltextmfs><input type=button name=vsttme class=buttoncolor value='Status' onClick=javascript:docStatus('"+servPath+"','"+dcrno+"')></font>");
        sb.append("<font class=tbltextmfs><input type=button name=prodentryd class=buttoncolor value='Product Entry' onClick=javascript:searchproductDR('"+servPath+"','"+dcrno+"')></font>");
        //sb.append("<font class=tbltextmfs><input type=button name=prodentryd class=buttoncolor value='Product Entry' onClick=window.navigate('"+servPath+"/eis.dataentry.DCRProduct_old1?dcrno="+dcrno+"')></font>");
        sb.append("<font class=tbltextmfs><input type=button name=giftentryd class=buttoncolor value='Gift Entry' onClick=javascript:searchgiftDR('"+servPath+"','"+dcrno+"')></font>");

        String[] drconvalter={"DCRD","DrConverted ","char","(3)","''"};
        valexists = ih.getsysprmcount("DoctorConverted");
        if(ih.remarkExists(dbname,valexists,"DoctorConverted",drconvalter,"N").equals("Y"))
        {
             //sb.append("<input type=\"hidden\" name=\"buttonexists\" value=\"Y\">");
             sb.append("<font class=\"tbltextmfs\"><input type=\"button\" name=\"drconvt\" class=\"buttoncolor\" value='Dr Converted' onClick=javascript:drconvert('"+servPath+"','"+dcrno+"')></font>");
        }
        else
        {
            sb.append("<input type=\"hidden\" name=\"buttonexists\" value=\"N\">");
        }
        //sb.append("<font class=tbltextmfs><input type=button name=drconvt class=buttoncolor value='Dr Converted' onClick=javascript:drconvert('"+servPath+"','"+dcrno+"')></font>");

        String[] drremarkalter={"DCRD","DrRemark ","varchar","(100)","''"};
        valexists = ih.getsysprmcount("DoctorWiseRemark");
        if(!remarkExists2(dbname,valexists,drremarkalter).equals(""))
        {
             sb.append("<input type=\"hidden\" name=\"buttonexists\" value=\"Y\">");
             sb.append("<font class=\"tbltextmfs\"><input type=\"button\" name=\"drremark\" class=\"buttoncolor\" value=\""+remarkExists2(dbname,valexists,drremarkalter)+"\" onClick=javascript:searchDrRemark('"+servPath+"','"+dcrno+"')></font>");
        }
        else
        {
            sb.append("<input type=\"hidden\" name=\"buttonexists\" value=\"N\">");
        }
        sb.append("<font class=tbltextmfs><input type=button name=expsentryd class=buttoncolor value='Delete' onClick=javascript:deleteConfirmDR('"+servPath+"','"+dcrhtcpid+"')></font");
        valexists = ih.getsysprmcount("otherremark");

        String []dralter={"DCRR"," ","varchar","(100)",""};
        String []column={"Cmarket ","Pstanding ","Cactivity ","SDPSR ","COD "};
        remarkExists(dbname,valexists,"otherremark",dralter,column,"N");
        if(checkRemark(con,"otherremark").equalsIgnoreCase("Y"))
           sb.append("<font class=\"tbltextmfs\"><input type=\"button\" name=\"otherremark\" class=\"buttondip\" value=\"OtherRemark\" onClick=javascript:searchOtherRemark('"+servPath+"','"+dcrno+"')></font></td></tr>");

        sb.append("</table>");
        return sb.toString();
    }

    private String getChemistDetails(Vector chemistdetails,String serial,String dcrhtcpid,String dcrno)
    {
        StringBuffer sb = new StringBuffer();
        Vector tempvector;
        if(chemistdetails.size() > 0)
        {
            for(int i=0;i < chemistdetails.size();)
            {
                tempvector = (Vector)chemistdetails.elementAt(i);
                String stcd = tempvector.elementAt(0).toString();
                String stname = tempvector.elementAt(1).toString();
                String stserial = tempvector.elementAt(2).toString();
                String check = "";
                if(stserial.equalsIgnoreCase(serial))
                    check= "checked";
                i++;
                if(i%4==1)
                    sb.append("<tr>");
                sb.append("<td width=25%><input type=radio name=drchc "+check+" value='CH"+stserial+"' onClick=javascript:ChRadio();><font face=verdana color=red>"+stcd.trim()+"</font>\t<font face=verdana color=black>"+stname.trim()+"</font></td>");
            }
        }
        sb.append("<input type=hidden name=elec value='"+chemistdetails.size()+"'>");

        if(chemistdetails.size()==0)
        sb.append("<tr><td class=linktabledata><center><font face=verdana size=2 color=red>No Records Exists</font></center></td></tr>");
        sb.append("</table>");
        sb.append("<table align=center>");
        sb.append("<tr><td><font class=tbltextmfs><input type=button name=giftentryc class=buttoncolor value='Gift' onClick=javascript:searchgiftCH('"+servPath+"','"+dcrno+"')></font>");
        sb.append("<font class=tbltextmfs><input type=button name=prodentryc class=buttoncolor value='Product / POB Entry' onClick=javascript:searchproductCH('"+servPath+"','"+dcrno+"')></font>");
        //sb.append("<font class=tbltextmfs><input type=button name=giftentryc class=buttoncolor value='DCR-Product' onClick=javascript:searchgiftCH('"+servPath+"','"+dcrno+"')></font>");
        sb.append("<font class=tbltextmfs><input type=button name=expsentryc class=buttoncolor value='Delete' onClick=javascript:deleteConfirmCH('"+servPath+"','"+dcrhtcpid+"')></font></td></tr>");
        sb.append("</table>");
        return sb.toString();
    }

    private String getExpenseDetails(Vector expensedetails,int emplvl,String allowance,String fare,String deduction)
    {
        StringBuffer sb = new StringBuffer();
        Vector tempvector;
        int i=0;

        if(expensedetails.size() > 0)
        {
            for(i=0;i<expensedetails.size();)
            {
                tempvector = (Vector)expensedetails.elementAt(i);
                String expdesc = tempvector.elementAt(0).toString();
                String expamt = tempvector.elementAt(1).toString();
                String expcd = tempvector.elementAt(2).toString();
                i++;

                if(i%4==1)
                    sb.append("<tr>");
                sb.append("<td width=25%><input type=radio name=drchx value='EX"+expcd+"' onClick=javascript:ExRadio();><font face=verdana color=black>"+expdesc.trim()+"</font>\t<font face=verdana color=red>"+expamt.trim()+"</font></td>");
            }
        }

        if(emplvl > 1)
        {
            if(!allowance.equalsIgnoreCase("0") && !allowance.equalsIgnoreCase(""))
            {
                i++;
                if(i%4==1)
                    sb.append("<tr>");
                    sb.append("<td width=25%><input type=radio name=drchx value='EA"+allowance+"' onClick=javascript:ExRadio();><font face=verdana color=black>ALLOWANCE</font>\t<font face=verdana color=red>"+allowance+"</font></td>");
            }

            if(!fare.equalsIgnoreCase("0.00") && !fare.equalsIgnoreCase(""))
            {
                i++;
                if(i%4==1)
                    sb.append("<tr>");
                    sb.append("<td width=25%><input type=radio name=drchx value='EF"+fare+"' onClick=javascript:ExRadio();><font face=verdana color=black>FARE</font>\t<font face=verdana color=red>"+fare+"</font></td>");
            }
        }
        if(!deduction.equalsIgnoreCase("0.00") && !deduction.equalsIgnoreCase(""))
        {
            i++;

            if(i%4==1)
                sb.append("<tr>");

            sb.append("<td width=25%><input type=radio name=drchx value='ED"+deduction+"' onClick=javascript:ExRadio();><font face=verdana color=black>DEDUCTION</font>\t<font face=verdana color=red>"+deduction+"</font></td>");
        }

        sb.append("<input type=hidden name=elex value='"+i+"'>");

        if(i==0)
            sb.append("<tr><td class=linktabledata><center><font face=verdana size=2 color=red>No Records Exists</font></center></td></tr>");

        return sb.toString();
    }

    private String getHeading(int emplvl,int option,String dbname)
    {
        StringBuffer sb = new StringBuffer();

        sb.append("<tr><td><font face=verdana size=4 color=red>JOINT WORKING :");

        if(emplvl > 1 && option != 3)
            sb.append("<td><font face=verdana size=2 color=black>"+getLevel1Heading(dbname)+" :");
            sb.append("<td><font face=verdana size=2 color=black>Area : ");
        if(emplvl==1)
        {
            String name ="wnetid";
            if(option==1)
                name+="d";
            else
                name+="c";
            sb.append("<input type=hidden name="+name+" value=\"\">");
        }
            return sb.toString();

    }

    private String getLevel1Heading(String dbname)
    {
        Connection con = null;
        Statement stmt = null;
        String retString = "";
        try
        {


            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='LEVEL1DESC'");
            if(rs.next())
                retString = rs.getString(1);
            rs.close();
        }
        catch(SQLException e)
        {
            Log.log(e.toString(),"Error");
        }
        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("Exception",e.toString());
            }
        }
            return retString;
    }

	private String getHoliDay(String dcrdate,String empcode,String dbname)
	{
	    String retString = "";
	    String holdates = "";
        Connection con = null;
        Statement stmt = null;

		try
	    {

            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();

            ResultSet rs1 = stmt.executeQuery("SELECT MAX(DCRDATE) FROM DCRH WHERE EMP='"+empcode+"' ");

            if(rs1.next())
            {
	            String sql = "SELECT HDATE FROM HOLMST WHERE ECODE='"+empcode+"' AND HDATE>'"+rs1.getString(1)+"' AND HDATE=DATE_ADD('"+dcrdate+"',INTERVAL -1 DAY) AND APRVCODE<>'' ORDER BY HDATE ASC";
                ResultSet rs = stmt.executeQuery(sql);

	            if(rs.next())
	                holdates=holdates + rs.getString(1);
	            if(!holdates.equalsIgnoreCase(""))
	                holdates += getHoliDay(holdates,empcode,dbname);
                    rs.close();
            }
        }
	    catch(SQLException e)
	    {
	            Log.log(e.toString(),"Error");
        }

        finally
        {
          try
          {
            if(stmt!=null)
                stmt.close();
            if(con!=null)
                con.close();
          } catch(Exception e)
          {
              Log.log("Exception ",e.toString());
          }
        }
	    return retString+=","+holdates ;
	}

    /*Logic for DoctorWiseRemark if doesnt exist add values in sysprm table
    else return the selected column*/
    private String remarkExists2(String dbname,String valexists,String[] dralter)
    {
          String svartxt = "";
          String altersql = "";
          Connection con = null;
          Statement stmt = null;

        try
        {

            con = ConnectionHandler.getConnection(dbname);
            stmt = con.createStatement();

            if(valexists.equals("0"))
            {
                String sysprmid="";
                ResultSet rs1 = stmt.executeQuery("select max(IdSysPrm)+1 as IDSYS from sysprm");
                while(rs1.next())
                {
                    sysprmid = rs1.getString("IDSYS");
                }
                stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values ( " +sysprmid+ ",'DoctorWiseRemark','N;')");
                rs1.close();
                altersql="ALTER TABLE ";
                for(int i=0;i<(dralter.length);i++)
                {
                    if(i==1)
                       altersql+=" ADD COLUMN ";
                    if(i!=0)
                        {
                        if(i%4==0 || (i==(dralter.length-1)))
                            altersql+=" DEFAULT ";
                        }
                    altersql+=dralter[i];
                    if(i!=0)
                        {
                        if(i%4==0 & (i!=(dralter.length-1)))
                            altersql+=",ADD COLUMN ";
                        }
                }
                stmt.executeUpdate(altersql);
            }
            else
            {
                ResultSet rs1 = stmt.executeQuery("SELECT mid(SVARTXT,3,length(svartxt)) as btnam FROM SYSPRM WHERE SVARNAME='DoctorWiseRemark' And Left(SvarTxt,1)='Y'");
                if(rs1.next())
                {
                    svartxt = rs1.getString(1);
                }
                rs1.close();
            }
        }
        catch(SQLException e)
        {
                Log.log(e.toString(),"Error");
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            }catch(Exception e)
            {
                Log.log("Error Occured",e.toString());
            }
        }
            svartxt = svartxt.replace(';',' ');

            return svartxt;
    }


    private String reasonExists2(String dbname,String valexists,String[] dralter)
        {
              String svartxt = "";
              String altersql = "";
              Connection con = null;
              Statement stmt = null;

            try
            {
                con = ConnectionHandler.getConnection(dbname);
                stmt = con.createStatement();

                if(valexists.equals("0"))
                {
                    String sysprmid="";
                    ResultSet rs1 = stmt.executeQuery("select max(IdSysPrm)+1 as IDSYS from sysprm");
                    while(rs1.next())
                    {
                        sysprmid = rs1.getString("IDSYS");
                    }
                    stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values ( " +sysprmid+ ",'DoctorWiseReason','N;')");
                    rs1.close();
                    altersql="ALTER TABLE ";
                    for(int i=0;i<(dralter.length);i++)
                    {
                        if(i==1)
                           altersql+=" ADD COLUMN ";
                        if(i!=0)
                            {
                            if(i%4==0 || (i==(dralter.length-1)))
                                altersql+=" DEFAULT ";
                            }
                        altersql+=dralter[i];
                        if(i!=0)
                            {
                            if(i%4==0 & (i!=(dralter.length-1)))
                                altersql+=",ADD COLUMN ";
                            }
                    }
                    stmt.executeUpdate(altersql);
                }
                else
                {
                    ResultSet rs1 = stmt.executeQuery("SELECT mid(SVARTXT,3,length(svartxt)) as btnam FROM SYSPRM WHERE SVARNAME='DoctorWiseReason' And Left(SvarTxt,1)='Y'");
                    if(rs1.next())
                    {
                        svartxt = rs1.getString(1);
                    }
                    rs1.close();
                }
            }
            catch(SQLException e)
            {
                    Log.log(e.toString(),"Error");
            }

            finally
            {
                try
                {
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                }catch(Exception e)
                {
                    Log.log("Error Occured",e.toString());
                }
            }
                svartxt = svartxt.replace(';',' ');

                return svartxt;
        }


    /*
        This method is for weekly logic only for client elder
    */

    private String dcrWeeklyValidationForElder(String dcrdate,String ecode,String netid,String dbname)
    {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try
        {
            con = ConnectionHandler.getConnection(dbname);

            stmt = con.createStatement();

            rs = stmt.executeQuery("Select CurDate() As CDate, Date_Format(Curdate(), '%w') As DOW");

            rs.next();

            String cdate = rs.getString("CDate");
            int dow = rs.getInt("DOW");
            rs.close();
            /*For Sundays dateofweek(DOW) changed from 0 to 7 */
            if(dow==0)
            {
                dow=7;
            }
            String sql = "SELECT ADDDATE('"+cdate+"',"+"-("+dow+"-1)) As WeekStartDate";
            rs = stmt.executeQuery(sql);

            rs.next();

            String weekstartdate = rs.getString("WeekStartDate");
            rs.close();

            rs = stmt.executeQuery("SELECT DATEDIFF('"+dcrdate+"','"+weekstartdate+"') As Diff");
            rs.next();

            if(rs.getInt("Diff") < 0)
            {
                dcrdate = weekstartdate;
            }
                checkDcrNotConfirmed(ecode,netid,con);
         } catch(Exception e)
         {
            Log.log("Exception dcr weekly validation for elder",e.toString());
         }
          finally
          {
            try
            {
                if(rs!=null)
                rs.close();
                if(stmt!=null)
                stmt.close();
                if(con!=null)
                con.close();
            } catch(Exception e)
            {
                Log.log("Exception finally",e.toString());
            }
        }
                return dcrdate;
    }
    private String dcrDaysValidationForElder(String dcrdate,String ecode,String netid,String dbname)
    {   Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try
        {
            con = ConnectionHandler.getConnection(dbname);
            stmt = con.createStatement();
            //rs = stmt.executeQuery("Select udate As CDate from lockunlock where ECODE='"+ecode+"' AND NETID='"+netid+"'");

            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";
            String val = "";
            String valcheck = "";
            String days = "";
            rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                val = rs.getString("SVarTxt");
            }
            rs.close();
            StringTokenizer st = new StringTokenizer(val,",");
            while(st.hasMoreTokens())
                {
                    valcheck=st.nextToken();
                    days=st.nextToken();
                }
            int valcheck1=Integer.parseInt(valcheck);
            rs = stmt.executeQuery("Select ADDDATE(curdate(),"+"-('" +valcheck1+ "')) As CDate");
            rs.next();

            String cdate = rs.getString("CDate");
            rs.close();
            rs = stmt.executeQuery("SELECT DATEDIFF('"+dcrdate+"','"+cdate+"') As Diff");
            rs.next();

            if(rs.getInt("Diff") < 0)
            {
                dcrdate = cdate;
            }
            checkDcrNotConfirmed(ecode,netid,con);
         }
        catch(Exception e)
         {
            Log.log("Exception dcr days validation for elder",e.toString());
         }
          finally
          {
            try
            {
                if(rs!=null)
                rs.close();
                if(stmt!=null)
                stmt.close();
                if(con!=null)
                con.close();
            } catch(Exception e)
            {
                Log.log("Exception finally",e.toString());
            }
          }
          return dcrdate;
    }

    private void checkDcrNotConfirmed(String ecode,String netid,Connection con)
    {
        try
        {
            Statement stmt = con.createStatement();
            String sql = "Select dcrno From Dcrh Where Dcrh.Emp='"+ecode+"' And NetId='"+netid+"'" +
                         " And Dcrh.Confdate='0000-00-00'";
            String dcrno = "";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next())
            {
                dcrno = rs.getString("DCRNo");
            }
                con.setAutoCommit(false);
                stmt.executeUpdate("DELETE FROM DCRR WHERE DCRNO='"+dcrno+"'");
                stmt.executeUpdate("DELETE FROM DCRP WHERE DCRNO='"+dcrno+"'");
                stmt.executeUpdate("DELETE FROM DCRE WHERE DCRNO='"+dcrno+"'");
                stmt.executeUpdate("DELETE FROM DCRD WHERE DCRNO='"+dcrno+"'");
                stmt.executeUpdate("DELETE FROM DCRH WHERE DCRNO='"+dcrno+"'");

                con.commit();
                rs.close();
        }
        catch(Exception e)
        {
            Log.log("Exception in dcrnot confirmed",e.toString());
            try
            {
                con.rollback();
            }catch(Exception ex)
            {
                Log.log("Exception",ex.toString());
            }
        }
    }

    /*
      This method is for checking in the sysprm whether entry exists
      after checking then check for a particular employee and netid to
      go for weekly or normal dcr
    */

    private boolean checkForNonMslDoc(Connection con)
    {
        boolean flag = false;

        Statement stmt = null;

        try
        {
            stmt = con.createStatement();

            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='NonMslDoctors'";
            String val = "";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                val = rs.getString("SVarTxt");
            }
            if(val.equalsIgnoreCase(""))
            {
                sql="select max(IdSysPrm)+1 as IDSYS from sysprm";
                String sysprmid="";
                rs = stmt.executeQuery(sql);
                while(rs.next())
                    {
                    sysprmid = rs.getString("IDSYS");
                    }
                stmt.executeUpdate("Insert into sysprm values('" +sysprmid+ "','NonMslDoctors','Y','')");
                checkForNonMslDoc(con);
                return true;
            }
            con.setAutoCommit(false);       //added by sagar
            con.commit();//added by sagar
            rs.close();
            if(val.equalsIgnoreCase("Y"))
                 return true;
            else
                 return false;

        } catch(SQLException e)
        {
            Log.log("Exception before",e.toString());
        }
            return flag;
    }

    private boolean checkForWeekDcr(Connection con,String ecode,String netid)
    {
        boolean flag = false;
        Statement stmt = null;

        try
        {
            stmt = con.createStatement();
            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";
            String val = "";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                val = rs.getString("SVarTxt");
            }
                con.setAutoCommit(false);       //added by sagar
                con.commit();//added by sagar
                rs.close();
            if(val.equals("W"))
            {

                sql = "SELECT DATEDIFF(ADDDATE(UDATE, 7),curdate()) AS NOOFDAYS FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"' ";

                rs = stmt.executeQuery(sql);
                int days = 0;
                while(rs.next())
                {
                    days = rs.getInt("NOOFDAYS");
                }
                    rs.close();
                    if(days >0 && days <8)
                        return false;
                    else
                    {
                        stmt.executeUpdate("DELETE FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"'");
                    }
                        return true;
            }
            else
            {
               return false;
            }
        }
        catch(SQLException e)
        {
            Log.log("Exception before",e.toString());

            String sql = "CREATE TABLE if not exists lockunlock ( " +
                                     " Ecode char(5) NOT NULL default \"\", "+
                                     " NetId char(4) NOT NULL default \"\", " +
                                     " udate date default \"0000-00-00\", " +
                                     " reason varchar(30) NOT NULL default \"\", " +
                                     " PRIMARY KEY  (Ecode,NetId)" +
                                     " ) ENGINE=MyISAM DEFAULT CHARSET=latin1";
            try
            {
                stmt.executeUpdate(sql);
                checkForWeekDcr(con,ecode,netid);
            }catch(SQLException ex)
            {
                Log.log("Exception after",ex.toString());
            }
        }

        finally
        {
            try
            {
                if(stmt!=null)
                stmt.close();
                con.rollback();               //added by sagar
            }catch(Exception e)
            {
                Log.log("Exception finally",e.toString());
            }
        }
            return flag;
    }

    private boolean checkWeekDcr(Connection con,String ecode,String netid)
    {
        boolean flag = false;

        Statement stmt = null;

        try
        {
            stmt = con.createStatement();
            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek'";
            String val = "";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                val = rs.getString("SVarTxt");
            }
                con.setAutoCommit(false);       //added by sagar
                con.commit();//added by sagar
                rs.close();
            if(val.equals("W"))
            {
                sql = "SELECT DATEDIFF(ADDDATE(UDATE,7),curdate()) AS NOOFDAYS FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"'";
                rs = stmt.executeQuery(sql);
                int days = 0;
                while(rs.next())
                {
                    days = rs.getInt("NOOFDAYS");
                }
                    rs.close();
                    if(days >0 & days <8)
                        return false;
                    else
                    {

                       // stmt.executeUpdate("DELETE FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"'");
                    }
                        return true;
            }
            else
            {
               return false;
            }
        } catch(SQLException e)
        {
            Log.log("Exception before",e.toString());

            String sql = "CREATE TABLE lockunlock ( " +
                                     " Ecode char(5) NOT NULL default \"\", "+
                                     " NetId char(4) NOT NULL default \"\", " +
                                     " udate date default \"0000-00-00\", " +
                                     " reason varchar(30) NOT NULL default \"\", " +
                                     " PRIMARY KEY (Ecode,NetId)" +
                                     " ) ENGINE=MyISAM DEFAULT CHARSET=latin1";
            try
            {
                stmt.executeUpdate(sql);
                checkForWeekDcr(con,ecode,netid);
            }catch(SQLException ex)
            {
                Log.log("Exception after",ex.toString());
            }
        }

        finally
        {
            try
            {
                if(stmt!=null)
                stmt.close();
                con.rollback();               //added by sagar
            }catch(Exception e)
            {
                Log.log("Exception finally",e.toString());
            }
        }
            return flag;
    }

   public String getdays(Connection con,String gracedays,String ecode,String netid)
   {
       String days2="";
       Statement stmt = null;
       String sql="";
       try
          {
              stmt = con.createStatement();
              sql = "SELECT IF((SELECT DATEDIFF(ADDDATE(UDATE,\""+gracedays+"\"),curdate()) FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"')!=\"\",(SELECT DATEDIFF(ADDDATE(UDATE, \""+gracedays+"\"),curdate()) FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"'),\"0\") AS NOOFDAYS";
             // Log.log("897 :"+sql);
              ResultSet rs = stmt.executeQuery(sql);

              while(rs.next())
              {
                  days2 = rs.getString("NOOFDAYS");
              }
                       rs.close();
                        }
                       catch(SQLException e)
                       {
                            Log.printStackTrace(e);
                       }
                //                 rs.close();
      return days2;
   }

    public boolean checkdcrdate(Connection con,String empcode)
    {
       boolean flg=false;
        try
        {
           Statement st=con.createStatement();
            ResultSet rs=st.executeQuery("select count(*) from lockunlock where ecode='"+empcode+"' ") ;
            if(rs.next())
            {
                // Log.log("record exist : ") ;
                if(rs.getInt(1)>0)
                    flg=false;
                else
                    flg=true;
            }

        }
        catch(Exception e)
        {
           Log.printStackTrace(e);
        }
        return flg;
    }

    public boolean checkForDaysDcr(Connection con,String ecode,String netid,String dcrdate)
    {
        boolean flag = false;
        Statement stmt = null;
        try
        {
            stmt = con.createStatement();
            String sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='DCRWeek' ";
            String val = "";
            String valcheck = "";
            String gracedays = "";
            int diffdate = 0;
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next())
            {
                val = rs.getString("SVarTxt");
            }
            con.setAutoCommit(false);
            //added by sagar
            con.commit();//added by sagar
            rs.close();
            //sql = "SELECT DateDiff(curdate(),\""+dcrdate+"\")+1 AS NOOFCHECKDAYS";
            sql = "SELECT DateDiff(curdate(),\""+dcrdate+"\") AS NOOFCHECKDAYS";

            rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                diffdate = rs.getInt("NOOFCHECKDAYS");
            }

            rs.close();

            StringTokenizer st = new StringTokenizer(val,",");
            while(st.hasMoreTokens())
                {
                    valcheck=st.nextToken();
                    gracedays=st.nextToken();
                }

            int valcheck1=Integer.parseInt(valcheck);

            if(valcheck1 == 0)
                return false;

            if(diffdate > valcheck1)
            {
                String days2 ="";
                days2=getdays(con,gracedays,ecode,netid);
                int gracedays1=Integer.parseInt(gracedays);
                int days=Integer.parseInt(days2);
                if((days<=gracedays1) && (days>0))
                     return false;   // do not lock
                else
                    {
                           Statement stmt1=con.createStatement();
                           ResultSet rslochistory=stmt1.executeQuery("select * from lockunlock where ecode='"+ecode+"' and netid='"+netid+"' ");
                           while(rslochistory.next())
                           {
                                 //ecode=rslochistory.getString(1);
                                 //netid=rslochistory.getString(2);
                                 String uldate=rslochistory.getString(3);
                                 String reason=rslochistory.getString(4);

                                 String sqllockunlock = "CREATE TABLE IF NOT EXISTS lockunlocklockunlockhistory ( " +
                                                        " Ecode char(5) NOT NULL default \"\", "+
                                                        " NetId char(4) NOT NULL default \"\", " +
                                                        " udate date default \"0000-00-00\", " +
                                                        " lockdate date default \"0000-00-00\" ,"+
                                                        " reason varchar(30) NOT NULL default \"\", " +
                                                        " PRIMARY KEY (Ecode,NetId,lockdate) " +
                                                        " ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ";


                                 stmt1.executeUpdate(sqllockunlock);
                                 stmt1.executeUpdate("insert into lockunlockhistory values ('"+ecode+"','"+netid+"','"+uldate+"',curdate(),'"+reason+"')");
                           }

                    stmt1.executeUpdate("DELETE FROM LOCKUNLOCK WHERE ECODE='"+ecode+"' AND NETID='"+netid+"'");
                    return false;    // lock
                    }
           }

            else
            {
               return false;
            }
        } catch(SQLException e)
        {
           // Log.log("Exception before",e.toString());
            String sql = "CREATE TABLE lockunlock (" +
                                     " Ecode char(5) NOT NULL default \"\", "+
                                     " NetId char(4) NOT NULL default \"\", " +
                                     " udate date default \"0000-00-00\", " +
                                     " reason varchar(30) NOT NULL default \"\", " +
                                     " PRIMARY KEY  (Ecode,NetId)" +
                                     " ) ENGINE=MyISAM DEFAULT CHARSET=latin1";

              try
            {
               stmt.executeUpdate(sql);
               checkForDaysDcr(con,ecode,netid,dcrdate);
            }catch(SQLException ex)
            {
                Log.log("Exception after",ex.toString());
            }
         }

        finally
        {
            try
            {
                if(stmt!=null)
                stmt.close();
                con.rollback();               //added by sagar
            }catch(Exception e)
            {
                Log.log("Exception finally",e.toString());
            }
        }
            return flag;
    }
     private String checkRemark(Connection con,String field)
     {
         String svartxt="";
         try{         Statement stmt=con.createStatement();
             ResultSet rs=stmt.executeQuery("SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='"+field+"'");
             if(rs.next())
             {
                svartxt=rs.getString(1);
             }
         }   catch(Exception e){}
         return svartxt;
     }

      public String remarkExists(String dbname,String valexists,String varname,String[] dralter,String[] column,String systxt)
       {
             String svartxt = "";
             Connection con = null;
             Statement stmt = null;
             String altersql="";

           try
           {
               con = ConnectionHandler.getConnection(dbname);
               stmt = con.createStatement();

               if(valexists.equals("0"))
               {
                   String sysprmid="";
                   ResultSet rs1 = stmt.executeQuery("select max(IdSysPrm)+1 as IDSYS from sysprm");
                   while(rs1.next())
                   {
                       sysprmid = rs1.getString("IDSYS");
                   }
                   stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values ( '" +sysprmid+ "','"+varname+"','"+systxt+"')");
                   rs1.close();
                   if(dralter.length>0)
                   {
                      for(int j=0;j<(column.length);j++)
                      {
                       altersql="ALTER TABLE ";
                       for(int i=0;i<(dralter.length);i++)
                       {
                            if(i==1)
                       altersql+=" ADD COLUMN "+column[j];
                            if(i!=0)
                        {
                        if(i%4==0 || (i==(dralter.length-1)))
                            altersql+=" ";
                        }
                    altersql+=dralter[i];
                    if(i!=0)
                        {
                        if(i%4==0 & (i!=(dralter.length-1)))
                          ;//  altersql+=",ADD COLUMN ";
                        }

                       }

                         stmt.executeUpdate(altersql);

                   }
                      }

               }
               else
               {

                   ResultSet rs1 = stmt.executeQuery("SELECT left(SVARTXT,1) AS SVARTXT FROM SYSPRM WHERE SVARNAME='"+varname+"'");
                   if(rs1.next())
                   {
                       svartxt = rs1.getString("SVARTXT");
                   }
                   rs1.close();
               }
           }
           catch(SQLException e)
           {
                   Log.log("2 "+e.toString());
           }

           finally
           {
               try
               {
                   if(con!=null)
                   con.close();
               }catch(Exception e)
               {
                   Log.log("Exception finally",e.toString());
               }
           }
               return svartxt;
      }

    private boolean MtpCheck(String dbname,HttpSession s,String empcode,String netid)
    {
        Connection con = null;
        Statement stmt = null;
        boolean flg=false;
        String level1="";String level2="";String level3="";String leveldate="";
        String todate="";String chkdate="";String fromdate="";
        Integer elevel=((Integer)s.getAttribute("Elevel"));
        try{
        con = ConnectionHandler.getConnection(dbname);
        checkMtpinSysprm(con);
        stmt=con.createStatement();
        String sql="SELECT SVarTxt FROM SYSPRM WHERE SVarName='MTP'";
        ResultSet rs=stmt.executeQuery(sql);

            if(rs.next()){
                StringTokenizer st=new StringTokenizer(rs.getString(1),",");
                while(st.hasMoreTokens())
                {
                    level1=st.nextToken();
                    level2=st.nextToken();
                    level3=st.nextToken();
                }
                if(elevel.intValue()==1){leveldate=level1;}
                else if(elevel.intValue()==2){leveldate=level2;}
                else leveldate=level3;
            }
                String wdate = s.getAttribute("WorkingDay").toString()+"-"+s.getAttribute("WorkingMonth").toString()+"-"+s.getAttribute("WorkingYear").toString();
                Calendar c = Calendar.getInstance();
                String day = Integer.toString(c.get(c.DATE));
                String year=s.getAttribute("WorkingYear").toString();
                String month=s.getAttribute("WorkingMonth").toString();
                if(month.equalsIgnoreCase("1")){
                    month="12";
                    year=String.valueOf(Integer.parseInt(year)-1);
                }
                else month=String.valueOf(Integer.parseInt(month)-1);
                if(month.length()<2)
                    month="0"+month;

                String wdate1=year+"-"+month+"-"+day;

                Statement stmt1=con.createStatement();
                String sql123="DROP TABLE IF EXISTS CHECKMTP"+empcode;
                stmt1.executeUpdate(sql123);
                String sql1="CREATE TEMPORARY TABLE CHECKMTP"+empcode+"(WDATE date);";
                stmt1.executeUpdate(sql1);

                String sql2="select day(last_day('"+wdate1+"')) as Totaldays";
                ResultSet rs2=stmt1.executeQuery(sql2);
                if(rs2.next())
                      todate=rs2.getString(1);

                InfoHelper ih = null;
                ih = new InfoHelper(con,empcode,dbname);

                for(int i=1;i<=Integer.parseInt(todate);i++)
                {
                    fromdate=Integer.toString(i);
                    if(fromdate.length()<2)
                        fromdate="0"+i;

                    chkdate=year+"-"+month+"-"+fromdate;

                    if(!(ih.isAHoliday(chkdate,empcode))){
                        String sql3="INSERT INTO CHECKMTP"+empcode+" VALUES('"+chkdate+"')";
                        stmt1.executeUpdate(sql3);
                    }
                 }

                String count1="";
                String sql4="SELECT COUNT(*) FROM CHECKMTP"+empcode;
                ResultSet rs4=stmt1.executeQuery(sql4);
                if(rs4.next())
                    count1=rs4.getString(1);

                String count2="";
                String sql5="SELECT COUNT(*) FROM MTP WHERE EMP='"+empcode+"' and NETID='"+netid+"' and WDate>='"+(year+"-"+month+"-01")+"' and WDate<='"+year+"-"+month+"-"+todate+"'";
                ResultSet rs5=stmt1.executeQuery(sql5);
                if(rs5.next())
                    count2=rs5.getString(1);

                if(!(count1.equalsIgnoreCase(count2)))
                {
                   int a=Integer.parseInt(day);
                   int b=Integer.parseInt(leveldate);
                   if(leveldate.equalsIgnoreCase("0"))
                        flg=false;
                   else if((a > b))
                        flg=true;
                }
              }
        catch(Exception e)
        {
          Log.log("3 "+e.toString());
          e.printStackTrace();
        }

        finally
        {
            try
            {
                if(stmt!=null)
                    stmt.close();
                if(con!=null)
                    con.close();
            } catch(Exception e)
            {
                Log.log("MTP Changes",e.toString());
            }
        }
    return flg;
    }

    private void checkMtpinSysprm(Connection con)
    {
        try{
        Statement stmt=con.createStatement();
        ResultSet rs=stmt.executeQuery("SELECT * FROM SYSPRM WHERE SVARNAME='MTP'");
        if(!rs.next()){
        ResultSet rs1=stmt.executeQuery("select max(idsysprm)+1 from sysprm");
        rs1.next();
        stmt.executeUpdate("insert into sysprm values('"+rs1.getString(1)+"','MTP','0,0,0','')");
        }
        }
        catch(Exception e)
        {
            Log.log("5 "+e.toString());
        }
    }
    private static String getsysprmcount(String varname,Connection con)
            {
                  String valexists="";
                try
                {
                    Statement stmt = con.createStatement();
                    ResultSet rs1 = stmt.executeQuery("SELECT count(*) as SVARTXT FROM SYSPRM WHERE SVARNAME='"+varname+"'");
                    if(rs1.next())
                    {
                      valexists = rs1.getString(1);
                    }
                    rs1.close();
                    stmt.close();
                }
                catch(SQLException e)
                {
                        Log.log(e.toString(),"Error");
                }
                    return valexists;
            }

        private static String remarkExists(String dbname,String valexists,String varname,String systxt)
            {
                  String svartxt = "";
                  Connection con = null;

                  String altersql="";

                  try
                  {
                    con = ConnectionHandler.getConnection(dbname);
                    Statement stmt = con.createStatement();

                    if(valexists.equals("0"))
                    {
                        String sysprmid="";
                        ResultSet rs1 = stmt.executeQuery("select max(IdSysPrm)+1 as IDSYS from sysprm");
                        while(rs1.next())
                        {
                            sysprmid = rs1.getString("IDSYS");
                        }
                        stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt) values ( '" +sysprmid+ "','"+varname+"','"+systxt+"')");
                        rs1.close();
                    }
                    else
                    {
                        ResultSet rs1 = stmt.executeQuery("SELECT left(SVARTXT,1) AS SVARTXT FROM SYSPRM WHERE SVARNAME='"+varname+"'");
                        if(rs1.next())
                        {
                            svartxt = rs1.getString("SVARTXT");
                          //  rem = rs1.getString("SVARTXT");
                        }
                        rs1.close();
                    }
                    stmt.close();
                }
                catch(SQLException e)
                {
                        Log.log(e.toString(),"Error");
                }
                finally
                 {
                     try
                     {
                         if(con!=null)
                             con.close();
                     } catch(Exception e)
                     {
                         Log.log("MTP Changes",e.toString());
                     }
                 }
            return svartxt;
          }
    public String getDrName(String cntcd,Connection con,String hcenetid )
    {
        String drname="";
        try
        {
        Statement stmt=con.createStatement();
        ResultSet rs=stmt.executeQuery("select drname from doched where cntcd='"+cntcd+"' and netid='"+hcenetid+"' ");
        if(rs.next())
            drname=rs.getString(1);

        rs.close();
        }
        catch(Exception e)
        {
            Log.log(e.toString(),"Error 555");
        }
     return drname;
    }

    public String getTcpidMTP(String dcrdate,String netid,String dbname,String empcode)
    {
        String tcpid="";
        try{
         Connection con=ConnectionHandler.getConnection(dbname);
         Statement stmt=con.createStatement();
         ResultSet rs=stmt.executeQuery("select tcpid from mtp where netid='"+netid+"' and wdate='"+dcrdate+"' and aprvcode<>'' and emp='"+empcode+"' ");
         if(rs.next())
            tcpid=rs.getString(1);

         con.close();
         rs.close();
        }
        catch(Exception e)
        {
            Log.log("6 "+e.toString());
        }
    return tcpid;
    }

    public String ShdCheck(String netid,String dcrdate,String dbname)
    {
      String tcp="";
      Connection con=null;

      try
      {
      con=ConnectionHandler.getConnection(dbname);
      Statement stmt=con.createStatement();

      Log.log("151515  : select t.tcpid,t.town,t.aprvcode from mtp m,tcphed t where m.netid='"+netid+"' and m.wdate='"+dcrdate+"' and m.tcpid=t.tcpid and m.netid=t.netid and t.aprvcode='' ");
      ResultSet rs=stmt.executeQuery("select t.tcpid,t.town,t.aprvcode from mtp m,tcphed t where m.netid='"+netid+"' and m.wdate='"+dcrdate+"' and m.tcpid=t.tcpid and m.netid=t.netid and t.aprvcode='' ");
      if(rs.next())
      {
        tcp=rs.getString(1)+"/"+rs.getString(2);
      }
      rs.close();
     }
      catch(Exception e)
      {
          Log.log("7 "+e.toString());
      }

//todo
     finally
     {
         try
         {
              if(con!=null)
                 con.close();
         } catch(Exception e)
         {
             Log.log("Connection Not Closing",e.toString());
         }
     }

    return tcp;
    }


    public String MTPAprv(String netid,String dcrdate,String dbname,String ecode)
    {
        String mtp="";
        int mtpcnt = 0;
        int holcnt = 0;
        int totaldays = 0;
        int jndate=0;
        Connection con = null;
        String mon = DateUtils.fillMonthNumber(DateUtils.toDMY(dcrdate).substring((DateUtils.toDMY(dcrdate).indexOf("/"))+1,DateUtils.toDMY(dcrdate).lastIndexOf("/")));
        String yr = DateUtils.toDMY(dcrdate).substring(DateUtils.toDMY(dcrdate).lastIndexOf("/")+1,DateUtils.toDMY(dcrdate).length());
        int days[] = {31,28,31,30,31,30,31,31,30,31,30,31};
     try
     {
      con=ConnectionHandler.getConnection(dbname);
      Statement stmt=con.createStatement();

         ResultSet rs1 = stmt.executeQuery("select count(*) cnt from mtp where netid='"+netid+"' and emp='" + ecode +"' and month(wdate)='"+mon+"' and year(wdate)='"+yr+"' and aprvcode <> ''");
         while(rs1.next())
         {
             mtpcnt = Integer.parseInt(rs1.getString(1));
         }

         ResultSet rs2 = stmt.executeQuery("select count(*) from holmst where month(hdate)='"+mon+"' and year(hdate)='"+yr+"' and ecode='"+ecode+"' and aprvcode <> ''");
         while(rs2.next())
         {
             holcnt = Integer.parseInt(rs2.getString(1));
         }

         ResultSet rs3=stmt.executeQuery("select day(max(joindate)) as d from nettrans where level1='"+ecode+"' and netid='"+netid+"' ") ;
         while(rs3.next())
         {
            jndate=rs3.getInt(1);
         }

         totaldays = days[(Integer.parseInt(mon))-1];
         totaldays=totaldays-jndate;
         if((mtpcnt + holcnt) >= totaldays)
         {
             mtp = netid;
         }
         stmt.close();
     }
     catch(Exception e)
     {
         Log.log("8"+e.toString());
     }
     finally
     {
         try
         {
             if(con!=null)
                 con.close();
         }
         catch(Exception e)
         {
             Log.log("Connection Not Closing",e.toString());
         }
     }
       return mtp;
    }

   /* private String getdrsamplegiven(String dcrno,Connection con)
    {
        String drname="";
        String cntcd="";
        String serial="";
        String drlist="";
        try{
        Statement stmt=con.createStatement();
        ResultSet rs1=null;
        ResultSet rs=stmt.executeQuery("select doched.drname,dcrd.cntcd,dcrd.serial from dcrd,doched where dcrd.custflg='D' and dcrno='"+dcrno+"' and dcrd.cntcd=doched.cntcd ");
        while(rs.next())
        {
            drname=rs.getString(1);
            cntcd=rs.getString(2);
            serial=rs.getString(3);
            Statement st=con.createStatement();
            rs1=st.executeQuery(" select count(*) from dcrd,doched,dcrp,prodmst where dcrd.custflg='D' "+
                                " and dcrd.dcrno='"+dcrno+"' and dcrd.cntcd='"+cntcd+"' and dcrd.cntcd=doched.cntcd "+
                                " and dcrp.dcrno=dcrd.dcrno and dcrd.serial = dcrp.serial  and dcrp.serial='"+serial+"' "+
                                " and prodmst.prodid=dcrp.prodid and prodmst.ptype=1 ");

            if(rs1.next())
            {
              if(rs1.getString(1).equalsIgnoreCase("0"))
              {
              drlist+=drname;
              drlist+=",";
              }
            }
        }

        }
        catch(Exception e)
        {
            Log.log("9"+e.toString());
        }
     return drlist;
    }
    */

    private String getdrsamplegiven(String dcrno,Connection con,String dcrdateyrmth)
    {
        String drname="";
        String cntcd="";
        String serial="";
        String drlist="";
        try{
        Statement stmt=con.createStatement();
         ResultSet rs1;
        ResultSet rs=stmt.executeQuery("select doched.drname,dcrd.cntcd,dcrd.serial " +
                " from dcrd,doched " +
                " where dcrd.custflg='D' and dcrno='"+dcrno+"' " +
                " and ( dcrd.cntcd=doched.cntcd AND dcrd.wnetid=doched.netid) " +
                " AND (doched.DELDATE='0000-00-00' OR CONCAT(SUBSTRING(doched.DELDATE,1,4),SUBSTRING(doched.DELDATE,6,2))>='"+dcrdateyrmth+"') ");

        while(rs.next())
        {
            drname=rs.getString(1);
            cntcd=rs.getString(2);
            serial=rs.getString(3);
            Statement st=con.createStatement();
            rs1=st.executeQuery("select count(*) from dcrd,doched,dcrp,prodmst where dcrd.custflg='D' "+
                                " and dcrd.dcrno='"+dcrno+"' and dcrd.cntcd='"+cntcd+"' and" +
                                " ( dcrd.cntcd=doched.cntcd and dcrd.wnetid=doched.netid ) "+
                                " and dcrp.dcrno=dcrd.dcrno and dcrd.serial = dcrp.serial  and dcrp.serial='"+serial+"' "+
                                " and prodmst.prodid=dcrp.prodid and prodmst.ptype=1  " +
                                " AND (doched.DELDATE='0000-00-00' OR CONCAT(SUBSTRING(doched.DELDATE,1,4),SUBSTRING(doched.DELDATE,6,2))>='"+dcrdateyrmth+"')");

            if(rs1.next())
            {
              if(rs1.getString(1).equalsIgnoreCase("0"))
              {
              drlist+=drname;
              drlist+=",";
              }

            }
        }

        }
        catch(Exception e)
        {
            Log.log(e.toString());
        }
     return drlist;
    }

    private String getdrgiftgiven(String dcrno,Connection con,String dcrdateyrmth)
    {
        String drname="";
        String cntcd="";
        String serial="";
        String drlist="";
        try
        {
        Statement stmt=con.createStatement();
        ResultSet rs1=null;
        ResultSet rs=stmt.executeQuery("select doched.drname,dcrd.cntcd,dcrd.serial " +
                " from dcrd,doched where dcrd.custflg='D' " +
                " and dcrno='"+dcrno+"' and ( dcrd.cntcd=doched.cntcd and dcrd.wnetid=doched.netid ) " +
                " AND (doched.DELDATE='0000-00-00' OR CONCAT(SUBSTRING(doched.DELDATE,1,4),SUBSTRING(doched.DELDATE,6,2))>='"+dcrdateyrmth+"') ");

        while(rs.next())
        {
            drname=rs.getString(1);
            cntcd=rs.getString(2);
            serial=rs.getString(3);
            Statement st=con.createStatement();
            rs1=st.executeQuery("select count(*) from dcrd,doched,dcrp,prodmst where dcrd.custflg='D' "+
                                " and dcrd.dcrno='"+dcrno+"' and dcrd.cntcd='"+cntcd+"' and" +
                                " ( dcrd.cntcd=doched.cntcd  and dcrd.wnetid=doched.netid )"+
                                " and dcrp.dcrno=dcrd.dcrno and dcrd.serial=dcrp.serial  and dcrp.serial='"+serial+"' "+
                                " and prodmst.prodid=dcrp.prodid and prodmst.ptype=2 " +
                                " AND (doched.DELDATE='0000-00-00' OR CONCAT(SUBSTRING(doched.DELDATE,1,4),SUBSTRING(doched.DELDATE,6,2))>='"+dcrdateyrmth+"') ");
            if(rs1.next())
            {
             // Log.log(""+rs1.getString(1));
              if(rs1.getString(1).equalsIgnoreCase("0"))
              {
              drlist+=drname;
              drlist+=",";
              }
            }
          }
          rs.close();
        }
        catch(Exception e)
        {
            Log.log("10 "+e.toString());
        }
     return drlist;
    }
    //_______________________________________________________
     private String getdrwwith(String dcrno,Connection con,String dcrdateyrmth)
    {
        String drlist="";
        try{
        Statement stmt=con.createStatement();
        ResultSet rs=stmt.executeQuery("select doched.drname from dcrd,doched " +
                " where dcrd.custflg='D' and dcrno='"+dcrno+"' " +
                " and ( dcrd.cntcd=doched.cntcd and dcrd.wnetid=doched.netid )" +
                " and dcrd.wrkwith='' " +
                " AND (Doched.DELDATE='0000-00-00' OR CONCAT(SUBSTRING(Doched.DELDATE,1,4),SUBSTRING(Doched.DELDATE,6,2))>='"+dcrdateyrmth+"')");
        if(rs.next())
        { rs.previous();
          while(rs.next())
          {
              drlist+=rs.getString(1);
              drlist+=",";
           }
         }
          rs.close();
        }
        catch(Exception e)
        {
            Log.log("11"+e.toString());
        }
     return drlist;
    }
    //________________________________________________________

    private String getcheckDrChExp(String dcrno,Connection con)
    {
        String tmp="";
        try
        {
        Statement stmt=con.createStatement();
        ResultSet rs=stmt.executeQuery("select count(*) from dcrd where custflg='D' and dcrno='"+dcrno+"' ");
        if(rs.next())
        {
            if(rs.getString(1).equalsIgnoreCase("0"))
            tmp+="Doctor";
        }

        ResultSet rs1=stmt.executeQuery("select count(*) from dcrd where custflg='C' and dcrno='"+dcrno+"' ");
        if(rs1.next())
        {
            if(rs1.getString(1).equalsIgnoreCase("0"))
            tmp+=" Chemist";
        }

       /*
       ResultSet rs2=stmt.executeQuery("select count(*) from dcre where dcrno='"+dcrno+"' ");
        if(rs2.next())
        {
            if(rs2.getString(1).equalsIgnoreCase("0"))
            tmp+=" Expense";
        }
        */

        if(!tmp.equalsIgnoreCase(""))
           tmp = tmp+" Section is Blank";

        stmt.close();
        rs.close();
        }
        catch(Exception e)
        {
            Log.log("12"+e.toString());
        }
     return tmp;
    }

    private String getcheckExp(String dcrno,Connection con)
    {
        String tmp="";

        try
        {
        Statement stmt=con.createStatement();
        ResultSet rs2=stmt.executeQuery("select count(*) from dcre where dcrno='"+dcrno+"' ");
        if(rs2.next())
        {
            if(rs2.getString(1).equalsIgnoreCase("0"))
            tmp +="Expense";
        }
        rs2.close();
        }
        catch(Exception e)
        {
            Log.log("13 "+e.toString());
        }
     return tmp;
    }

    private String getcheckTcpid(String dcrno,Connection con)
       {
           String tmp="";

           try{
               Statement stmt =con.createStatement();
               ResultSet rs2=stmt.executeQuery("select wrkflg from dcrh where dcrno='"+dcrno+"' ");
               if(rs2.next())
               {
                 tmp +=rs2.getString(1);
               }
               rs2.close();
               stmt.close();
              }
              catch(Exception e)
              {
               Log.log("14 "+e.toString());
              }
              return tmp;
       }

 private String checkcountactualtotaldays(String dbname,HttpSession s,String empcode,String netid)
          {
            Connection con = null;

            String  countactualdays="";

            boolean flg=false;

            String jnd="",jnm="",jny="";

            int j=1;

            String level1="";String level2="";String level3="";String leveldate="";
            String todate="";String chkdate="";String fromdate="";

            Integer elevel=((Integer)s.getAttribute("Elevel"));
            try
            {
            con = ConnectionHandler.getConnection(dbname);
            checkMtpinSysprm(con);
            Statement stmt=con.createStatement();
            String sql="SELECT SVarTxt FROM SYSPRM WHERE SVarName='MTP'";

            ResultSet rs=stmt.executeQuery(sql);

            if(rs.next())
            {
                    StringTokenizer st=new StringTokenizer(rs.getString(1),",");
                    while(st.hasMoreTokens())
                    {
                        level1=st.nextToken();
                        level2=st.nextToken();
                        level3=st.nextToken();
                    }
                    if(elevel.intValue()==1){leveldate=level1;}
                    else if(elevel.intValue()==2){leveldate=level2;}
                    else leveldate=level3;
            }

            String wdate1=s.getAttribute("DCRDate").toString();
           // Log.log("wdate1 : "+wdate1);

            String month = DateUtils.fillMonthNumber(DateUtils.toDMY(wdate1).substring((DateUtils.toDMY(wdate1).indexOf("/"))+1,DateUtils.toDMY(wdate1).lastIndexOf("/")));
            String year = DateUtils.toDMY(wdate1).substring(DateUtils.toDMY(wdate1).lastIndexOf("/")+1,DateUtils.toDMY(wdate1).length());

            Statement stmt1=con.createStatement();
            String sql123="DROP TABLE IF EXISTS tempchkmtp"+empcode;
            stmt1.executeUpdate(sql123);
            String sql1="CREATE TEMPORARY TABLE tempchkmtp"+empcode+" (WDATE date);";
            stmt1.executeUpdate(sql1);

            String sql2="select day(last_day('"+wdate1+"')) as Totaldays";
            ResultSet rs2 = stmt1.executeQuery(sql2);
            if(rs2.next())
                 todate=rs2.getString(1);

            rs2.close();
            //Log.log("101020 :"+todate);
            InfoHelper ih = null;
            ih = new InfoHelper(con,empcode,dbname);
            ResultSet rs3=stmt.executeQuery("select day(min(joindate)) as D,month(min(joindate)) as M,year(min(joindate)) as Y from nettrans where level1='"+empcode+"' and netid='"+netid+"' and mth='"+month+"' and yr='"+year+"'");
           //Log.log("101212 : select day(min(joindate)) as D,month(min(joindate)) as M,year(min(joindate)) as Y from nettrans where level1='"+empcode+"' and netid='"+netid+"' and mth='"+month+"' and yr='"+year+"'");
            while(rs3.next())
            {
              jnd=rs3.getString(1);
              jnm=rs3.getString(2);
              jny=rs3.getString(3);
            }
            rs3.close();

             if(jnm.length()<2)
                 jnm="0"+jnm;

             if(month.equalsIgnoreCase(jnm) && year.equalsIgnoreCase(jny))
             {
                 j=Integer.parseInt(jnd);
             }

             for(int i=j;i<=Integer.parseInt(todate);i++)
             {
                fromdate=Integer.toString(i);
                if(fromdate.length()<2)
                     fromdate="0"+i;

                chkdate=year+"-"+month+"-"+fromdate;
                if(!(ih.isAHoliday(chkdate,empcode)))
                {
                   String sql3="INSERT INTO tempchkmtp"+empcode+" VALUES ('"+chkdate+"') ";
                   stmt1.executeUpdate(sql3);
                }
              }
                    String count1="";
                    String days=""+j;
                    if(days.length()<2)
                        days="0"+days;
                    String sql4="select DAY(TEMPCHKMTP"+empcode+".WDATE) from tempchkmtp"+empcode+" LEFT join mtp "+
                                " on tempchkmtp"+empcode+".wdate = mtp.WDate AND mtp.emp='"+empcode+"' and mtp.wdate>='"+year+"-"+month+"-"+days+"' and mtp.wdate<='"+year+"-"+month+"-"+todate+"' and aprvcode<> '' "+
                                " WHERE MTP.WDATE IS NULL";

                   // Log.log("504 : "+sql4);
                    ResultSet rs4=stmt1.executeQuery(sql4);
                    while(rs4.next())
                    {
                      countactualdays+=rs4.getString(1)+",";
                    }
                    rs4.close();
                    rs.close();
            }
            catch(Exception e)
            {
                Log.log("15 "+e.toString());
                Log.printStackTrace(e);
            }
            finally
            {
                try
                {
                    if(con!=null)
                        con.close();
                } catch(Exception e)
                {
                    Log.log("MTP Changes",e.toString());
                }
            }
        return countactualdays;
        }
    //*************************************************************************************

      private String checkcountactualtotaldays1(String dbname,HttpSession s,String empcode,String netid)
        {
           // Log.log("501 : ");
            String mthmtpnotfeed="";
            createtable(wmth,wyr,empcode,netid,dbname) ;
            String mtharr[]=new String[4];
            mtharr=mtharr(wmth,wyr,dbname);
            Connection con;
            con = ConnectionHandler.getConnection(dbname);
            try{
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery("select * from finaltable where ecode='"+empcode+"' and netid='"+netid+"'");
            if(rs.next())
            {
                 if(rs.getInt(2)!=0)
                   mthmtpnotfeed=mtharr[0]+":-"+rs.getString(2);
                 if(rs.getInt(3)!=0)
                   mthmtpnotfeed+=mtharr[1]+":-"+rs.getString(3);
                 if(rs.getInt(4)!=0)
                   mthmtpnotfeed+=mtharr[2]+":-"+rs.getString(4);
                if(rs.getInt(5)!=0)
                   mthmtpnotfeed+=mtharr[3]+":-"+rs.getString(5);
            }
            rs.close();
            st.close();
            con.close();
            }
          catch(Exception e)
            {
            }
          /*  boolean flag=true;
            Connection con = null;
            Statement stmt = null,ststartdate=null;
            String  countactualdays="";

            String level1="",level2="",level3="",leveldate="" ;
            String startmonth="",startdate="",dtarr="" ;

           // String dtarr[]=new String[3];
            Integer elevel=((Integer)s.getAttribute("Elevel"));
            try
            {
            con = ConnectionHandler.getConnection(dbname);
            checkMtpinSysprm(con);
           //*************************************************************************************888
            ststartdate=con.createStatement();
            ResultSet rsstartdate=ststartdate.executeQuery("select month(svartxt) from sysprm where svarname='startdate' ");

            if(rsstartdate.next())
            {
             startmonth=rsstartdate.getString(1);
            }

            String wdate1="";
            wdate1 = s.getAttribute("WorkingYear").toString() + "-" + s.getAttribute("WorkingMonth").toString() + "-" + s.getAttribute("WorkingDay").toString();
            //Log.log("WDATE1 "+wdate1);
            Calendar c = Calendar.getInstance();
            String currdate = c.get(c.YEAR)+"-"+(c.get(c.MONTH)+1)+"-"+c.get(c.DATE);
            wdate1=currdate;

            //Log.log(" W "+wdate1);
            String month = DateUtils.fillMonthNumber(DateUtils.toDMY(wdate1).substring((DateUtils.toDMY(wdate1).indexOf("/"))+1,DateUtils.toDMY(wdate1).lastIndexOf("/")));
            String year = DateUtils.toDMY(wdate1).substring(DateUtils.toDMY(wdate1).lastIndexOf("/")+1,DateUtils.toDMY(wdate1).length());

            Statement sttemp=con.createStatement();
            sttemp.executeUpdate("create temporary table date( d date)");
            sttemp.executeUpdate("insert into date values ('"+wdate1+"')");

            startdate=year+"-"+startmonth+"-"+"01";
            dtarr = startdate;
            String dt="";
            for(int i=1;(i<3)&&(flag==true);i++)
            {
                //dtarr[i]=startdate;
                 while(flag==true)
                 {
                   dt=dtarr ;
                   Statement stdate=con.createStatement();
                   ResultSet rsdate=stdate.executeQuery("select date_add('"+startdate+"',interval+4 month)");
                   if(rsdate.next())
                   {
                     startdate=rsdate.getString(1);
                     dtarr=startdate;
                    // Log.log("DATE : "+dt+" DATE1 : "+dtarr);
                   }
                   sttemp=con.createStatement();
                   ResultSet rs=sttemp.executeQuery("select d from date where d>='"+dt+"' and d<='"+dtarr+"' ") ;
                   if(rs.next())
                   {
                    // Log.log("dcrdate :"+rs.getString(1));
                     dtarr=dt;
                     flag=false;
                   }
                  }
            }
                Statement stdate=con.createStatement();
                ResultSet rsdate=stdate.executeQuery("select month('"+dtarr+"')") ;
                if(rsdate.next())
                {
                  month=rsdate.getString(1);
                }

             //***********************************************************************

           if(flag==false)
            {
            stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery("SELECT SVarTxt FROM SYSPRM WHERE SVarName='MTP'");

            if(rs.next())
            {
                    StringTokenizer st=new StringTokenizer(rs.getString(1),",");
                    while(st.hasMoreTokens())
                    {
                        level1=st.nextToken();
                        level2=st.nextToken();
                        level3=st.nextToken();
                    }
                    if(elevel.intValue()==1)
                    {
                        leveldate=level1;
                    }
                    else if(elevel.intValue()==2)
                    {
                        leveldate=level2;
                    }
                    else leveldate=level3;
              }

            int mthmtp=0,hmth=0,totdays=0;
            mthname="";
            int mthwisecount[];
		    StringBuffer sb = new StringBuffer();
            int month1=Integer.parseInt(month);

            PreparedStatement stcount=con.prepareStatement(" Select Count(*) from mtp where month(wdate)=? and year(wdate)=? and emp=? and aprvcode<> ''");
            PreparedStatement stHcount=con.prepareStatement("select count(*) from holmst where month(hdate)=?  and year(hdate)=? and ecode=? ");
            mthwisecount=new int[4];

            for( int k=0;k<4;k++)
            {
                mthmtp=0;
                String firstdate=year+"-"+month+"-"+"1";
                Statement stTcount=con.createStatement();

                stcount.setString(1,""+month1);
                stcount.setString(2,year);
                stcount.setString(3,""+empcode);

                stHcount.setString(1,""+month1);
                stHcount.setString(2,year);
                stHcount.setString(3,""+empcode);

                ResultSet rsTcount=stTcount.executeQuery("select day(Last_day('"+firstdate+"'))");
                if(rsTcount.next())
                {
                    totdays=rsTcount.getInt(1);
                }
               // ResultSet rscount=stcount.executeQuery("Select Count(*) from mtp where month(wdate)='"+month+"' and year(wdate)='"+year+"' and emp='"+emp+"' ");

                ResultSet rscount=stcount.executeQuery();
                if(rscount.next())
                {
                 mthmtp=rscount.getInt(1);
                }

                //ResultSet rsHcount=stHcount.executeQuery("select count(*) from holmst where month(hdate)='"+month+"'  and year(hdate)='"+year+"' and ecode='"+emp+"' ");
                ResultSet rsHcount=stHcount.executeQuery();
                if(rsHcount.next())
                {
                  hmth=rsHcount.getInt(1);
                }

                totdays=totdays-hmth;
                if(mthmtp<totdays)
                {
                  mthname+=" "+DateUtils.getMonthName(month1);
                  mthwisecount[k]=totdays-mthmtp;
                  mthname+= ": "+mthwisecount[k]+",";
                }
               if(mthmtp>=totdays)
               {
                 mthwisecount[k]=0;
               }


                if(month1==12)
                  {
                     month1=1;
                     int Year=Integer.parseInt(year)+1;
                     year=""+Year;
                   }

                 month1=month1+1;
              }
             }
                countactualdays=mthname;
            }
            catch(Exception e)
            {
                Log.log(e.toString());
                e.printStackTrace();
            }
            finally
            {
                try
                {
                    if(stmt!=null)
                        stmt.close();
                    if(con!=null)
                        con.close();
                } catch(Exception e)
                {
                    Log.log("MTP Changes",e.toString());
                }
            }       */
        return mthmtpnotfeed;
        }

    private String checkMTPFill(String dbname,HttpSession s,String empcode,String netid,String systpvar)
            {
                String missedmtp="";
                //if(systpvar.equalsIgnoreCase("Y"))
                //    {
                //       missedmtp=checkcountactualtotaldays1(dbname,s,s.getAttribute("DCREmp").toString(),s.getAttribute("DCRNetID").toString());
                //    }
                //else
                    if(systpvar.equalsIgnoreCase("N"))
                    {
                       missedmtp=checkcountactualtotaldays(dbname,s,s.getAttribute("DCREmp").toString(),s.getAttribute("DCRNetID").toString());
                    }
                return missedmtp;
            }

    private String createtable(String mth,String year,String ecode,String netid,String dbname)
          {
              //TODO
              Connection con;
              con = ConnectionHandler.getConnection(dbname);
              boolean flag=true;
              int mthmtp=0,hmth=0,totdays=0;
              mthname="";
              int mthwisecount[];
              StringBuffer sb = new StringBuffer();
              try
              {
                  String startmonth="",startdate="",dtarr="";
                  int month=Integer.parseInt(mth);

                  String wdate1=year+"-"+mth+"-1";
                  Statement ststartdate=con.createStatement();
                  ResultSet rsstartdate=ststartdate.executeQuery("select month(svartxt) from sysprm where svarname='startdate' ");

                  if(rsstartdate.next())
                  {
                   startmonth=rsstartdate.getString(1);
                  }

                            Statement sttemp=con.createStatement();
                            sttemp.executeUpdate("DROP TABLE IF EXISTS date");
                            sttemp.executeUpdate("create table date( d date)");
                            sttemp.executeUpdate("insert into date values('"+wdate1+"')");

                            startdate=year+"-"+startmonth+"-"+"01";
                            dtarr = startdate;
                            String dt="";
                            for(int i=1;(i<3)&&(flag==true);i++)
                            {
                                //dtarr[i]=startdate;
                                 while(flag==true)
                                 {
                                   dt=dtarr ;
                                   Statement stdate=con.createStatement();
                                   ResultSet rsdate=stdate.executeQuery("select date_add('"+startdate+"',interval+4 month)");
                                   if(rsdate.next())
                                   {
                                     startdate=rsdate.getString(1);
                                     dtarr=startdate;
                                   }


                                   sttemp=con.createStatement();
                                   ResultSet rs=sttemp.executeQuery("select d from date where d>='"+dt+"' and d<'"+dtarr+"' ") ;
                                   if(rs.next())
                                   {
                                     dtarr=dt;
                                     flag=false;
                                   }
                                   rs.close();
                                  }
                             }
                                Statement stdate=con.createStatement();
                                ResultSet rsdate=stdate.executeQuery("select month('"+dtarr+"')") ;
                                if(rsdate.next())
                                {
                                  month=rsdate.getInt(1);
                                }

                 mthwisecount=new int[4];
                 stdate.executeUpdate("DROP TABLE IF EXISTS tempmtp");
                 stdate.executeUpdate("DROP TABLE IF EXISTS actualdaystpreq");
                 stdate.executeUpdate("DROP TABLE IF EXISTS FINALTABLE");
                 stdate.close();

                 String tempmtp="create temporary table tempmtp select netid,emp ";
                 String actualdaystpreq="create temporary table actualdaystpreq select ecode ";


                  for(int j=1;j<=4;j++)
                  {
                       if(month>12)
                      {
                          month=1;
                          int Year=Integer.parseInt(year)+1;
                          year=""+Year;
                      }
                      String firstdate=year+"-"+month+"-"+"1";

                     Statement stTcount=con.createStatement();
                     ResultSet rsTcount=stTcount.executeQuery("select day(Last_day('"+firstdate+"'))");
                      if(rsTcount.next())
                      {
                          totdays=rsTcount.getInt(1);
                      }

                      String lastdate=year+"-"+month+"-"+totdays;
                      tempmtp+=",sum(if(wdate>='"+firstdate+"' && wdate<='"+lastdate+"',1,0)) Mtp"+j+"mth ";
                      actualdaystpreq+=",("+totdays+"-(sum(if(hdate>='"+firstdate+"' && hdate<='"+lastdate+"',1,0)))) H"+j+"mth ";
                      month++;
                  }


                  tempmtp+=" from mtp where mtp.emp='"+ecode+"' and aprvcode <> '' group by netid ";
                  actualdaystpreq+=" from holmst where holmst.ecode='"+ecode+"' and aprvcode <> '' group by ecode ";

                  Statement stcreatetable=con.createStatement();
                  stcreatetable.executeUpdate(tempmtp);
                  stcreatetable.executeUpdate(actualdaystpreq);

                  String Finaltable="create table finaltable select distinct(ecode) as ecode,if((h1mth-mtp1mth)<0,'0',(h1mth-mtp1mth)) as nfedfmthtp,if((h2mth-mtp2mth)<0,0,(h2mth-mtp2mth)) as nfedsmthtp ,if((h3mth-mtp3mth)<0,0,(h3mth-mtp3mth)) as nfedtmthtp,if((h4mth-mtp4mth)<0,0,(h4mth-mtp4mth)) as nfedfrmthtp,netid from tempmtp,actualdaystpreq where tempmtp.emp=actualdaystpreq.ecode ";
                  stcreatetable.executeUpdate(Finaltable);

              }// try closes
              catch(SQLException e)
              {
                  Log.printStackTrace(e);
              } // catch closes

              return sb.toString();
          }// createtables closes

     private String[] mtharr(String mth,String year,String dbname)
	 {
                 String mtharr[]=new String[4];
                 Connection con;
                 con = ConnectionHandler.getConnection(dbname);
                 boolean flag=true;
                 String startmonth="",startdate="",dtarr="";
                 int month=Integer.parseInt(mth);
                 String wdate1=year+"-"+mth+"-1";
                 try{
                   Statement ststartdate=con.createStatement();
                   ResultSet rsstartdate=ststartdate.executeQuery("select month(svartxt) from sysprm where svarname='startdate' ");

                   if(rsstartdate.next())
                   {
                    startmonth=rsstartdate.getString(1);
                   }
                   ststartdate.close();rsstartdate.close();

                             Statement sttemp=con.createStatement();
                             sttemp.executeUpdate("DROP TABLE IF EXISTS date");
                             sttemp.executeUpdate("create table date( d date)");
                             sttemp.executeUpdate("insert into date values ('"+wdate1+"')");

                             startdate=year+"-"+startmonth+"-"+"01";
                             dtarr = startdate;
                             String dt="";
                             for(int i=1;(i<3)&&(flag==true);i++)
                             {
                                  while(flag==true)
                                  {
                                    dt=dtarr ;
                                    Statement stdate=con.createStatement();
                                    ResultSet rsdate=stdate.executeQuery("select date_add('"+startdate+"',interval+4 month)");
                                    if(rsdate.next())
                                    {
                                      startdate=rsdate.getString(1);
                                      dtarr=startdate;
                                    }

                                    sttemp=con.createStatement();
                                    ResultSet rs=sttemp.executeQuery("select d from date where d>='"+dt+"' and d<'"+dtarr+"' ") ;
                                    if(rs.next())
                                    {
                                      dtarr=dt;
                                      flag=false;
                                    }
                                   }
                              }
                                 Statement stdate=con.createStatement();
                                 ResultSet rsdate=stdate.executeQuery("select month('"+dtarr+"')") ;
                                 if(rsdate.next())
                                 {
                                   month=rsdate.getInt(1);
                                 }

                     }
                      catch(SQLException  e)
                     {
                     e.printStackTrace();
                     }
        for(int i=0;i<4;i++)
        {
            if(month>12)
               month=1;
            mtharr[i]=DateUtils.getMonthName(month);
            month=month+1;
        }
        return mtharr;
	} // mthname

    private boolean ExpCheck(Connection con)
       {
           boolean flag=false;
           try
           {
           Statement stmt=con.createStatement();
           ResultSet rs=stmt.executeQuery("SELECT count(*) FROM EXPCTR");

           if(rs.next())
             {
                if(rs.getInt(1)>0)
                   flag=true;
             }
           }
           catch(Exception e)
           {
               Log.log("101 :"+e.toString());
           }
           return flag;
       }

    private boolean LeavePendingCheck(Connection con,String dcrdate,String empcode)
       {
           boolean LAprvPending=false;
           try
           {

                    String  sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='LeaveChecking'";
                    String Lchecking="";
                    ResultSet rs = con.createStatement().executeQuery(sql);

                     if(rs.next())
                       Lchecking=rs.getString(1);

                     if(Lchecking.equalsIgnoreCase("Y"))
                     {
                        con.createStatement().executeUpdate("create temporary table zzpendingleavestatus select LeaveRequest.startDate,LeaveRequest.endDate,LeaveRequest.Ltype,LeaveRequest.LReason,if(isnull(leavemapping.status),'Pending',leavemapping.status) as status,LeaveRequest.leaveid as leaveid " +
                                  " from leavemapping " +
                                  " right join LeaveRequest " +
                                  " on LeaveRequest.LeaveId=leavemapping.LeaveId where '"+dcrdate+"'>=startdate and '"+dcrdate+"'<=enddate and LeaveRequest.ecode='"+empcode+"' order by LeaveRequest.startdate desc");

                        ResultSet rsLAprvPending=con.createStatement().executeQuery("select count(*) from zzpendingleavestatus where status='Pending' ");
                        if(rsLAprvPending.next())
                        {
                            if(rsLAprvPending.getInt(1)>0)
                               LAprvPending=true;
                        }
                        con.createStatement().executeUpdate("drop table if exists zzpendingleavestatus");
                     }
           }
           catch(Exception e)
           {
               Log.log("101 :"+e.toString());
           }
           return LAprvPending;
       }

    private boolean SampleOpeningCheck(Connection con,String netid,String financialyr,String dcrdate)
       {
           boolean flag=false;
           try
           {

                        PreparedStatement stmt=null;
                        String sql = "SELECT count(*) as SVARTXT FROM SYSPRM WHERE SVARNAME='SampleOpeningCheck' ";
                        stmt = con.prepareStatement(sql);
                        String valueexist="";
                        ResultSet rs = stmt.executeQuery();
                        while(rs.next())
                        {
                          valueexist = rs.getString("SVarTxt");
                        }
                        if(valueexist.equals("0"))
                        {
                             sql="select max(IdSysPrm)+1 as IDSYS from sysprm";
                             stmt = con.prepareStatement(sql);
                             String sysprmid="";
                             rs = stmt.executeQuery();
                             while(rs.next())
                             {
                                sysprmid = rs.getString("IDSYS");
                             }

                             stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt,comment) values(" +sysprmid+ ",'SampleOpeningCheck','N','Y for checking opening balance while enering DCR and N for not checking opening balance while entering DCR')");
                             rs.close();
                        }

                        sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='SampleOpeningCheck'";
                        stmt = con.prepareStatement(sql);
                        String samchecking="";
                        rs = stmt.executeQuery();
                        if(rs.next())
                          samchecking=rs.getString(1);

             if(samchecking.equalsIgnoreCase("Y"))
             {
                 //sql="select * from sample where netid='"+netid+"' and yr='"+financialyr+"' and opbal>0 ";
                 String sampleyr=dcrdate.substring(0,2);
                 int nextyr = Integer.parseInt(sampleyr)+1;
                 String nyr=""+nextyr;
                 while (nyr.length()<2)
                     nyr="0"+ nyr;
                 sampleyr=sampleyr+nyr;

                 //sql="select * from sample where netid='"+netid+"' and yr='"+sampleyr+"' and opbal>0 ";
                 sql="select count(*) from sample where netid='"+netid+"' and yr='"+financialyr+"' and opbal>0 ";
                // Log.log(""+sql);
                 stmt = con.prepareStatement(sql);
                 rs = stmt.executeQuery();
                 if(rs.next())
                 {
                    if(rs.getInt(1)==0)
                    {
                        flag=true;
                        sql="select * from sample where netid='"+netid+"' and yr='"+financialyr+"' ";
                    }
                    else
                    {
                     flag=false;
                    }
                 }
             }
           }
           catch(Exception e)
           {
               Log.printStackTrace(e);
           }
           return flag;
       }

     private boolean LeaveChecking(Connection con)
       {
           boolean flag=false;
           try
           {

                        PreparedStatement stmt=null;
                        String sql = "SELECT count(*) as SVARTXT FROM SYSPRM WHERE SVARNAME='LeaveChecking' ";
                        stmt = con.prepareStatement(sql);
                        String valueexist="";
                        ResultSet rs = stmt.executeQuery();
                        while(rs.next())
                        {
                          valueexist = rs.getString("SVarTxt");
                        }
                        if(valueexist.equals("0"))
                        {
                             sql="select max(IdSysPrm)+1 as IDSYS from sysprm";
                             stmt = con.prepareStatement(sql);
                             String sysprmid="";
                             rs = stmt.executeQuery();
                             while(rs.next())
                             {
                                sysprmid = rs.getString("IDSYS");
                             }

                             stmt.executeUpdate("Insert into SYSPRM (IDSysPrm,SVarName,SVarTxt,comment) values(" +sysprmid+ ",'LeaveChecking','N','Y for Leave Checking and N to stop Leave Checking while entering DCR.')");
                             rs.close();
                        }

                        sql = "SELECT SVARTXT FROM SYSPRM WHERE SVARNAME='LeaveChecking'";
                        stmt = con.prepareStatement(sql);
                        String Lchecking="";
                        rs = stmt.executeQuery();
                        if(rs.next())
                          Lchecking=rs.getString(1);

             if(Lchecking.equalsIgnoreCase("Y"))
             {
                    flag=true;
             }

           }
           catch(Exception e)
           {
               Log.printStackTrace(e);
           }
           return flag;
       }
}// class closes

[sarika81286]

i have used global out object of printwriter class is it giving problem of data misplacing within different session.

if u have any idea then plz reply thanks in advance.....