locked
Need urgent help with (0x80131904): RRS feed

  • Question

  • User2080995897 posted

    I am sort of a newbie with .NET technology....While opening the Retail Sales option from the system i got this error.... "Exception Details: System.Exception: System.Data.SqlClient.SqlException (0x80131904): Ambiguous column name 'EnquiryNo'." Below i mention the Exception details please help me with it asap

    Exception Details: System.Exception: System.Data.SqlClient.SqlException (0x80131904): Ambiguous column name 'EnquiryNo'.
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
      at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
      at LloydsDAL.DBManupulation.GetDataTable(String strSQL) in C:\lloyds\LloydsDAL\DBManipulation.cs:line 69
    
    Source Error: 
    
    
    Line 3077:            string strSQL = "select paymenthdr.*,paymentDtl.Amount,Convert(varchar(10),EnquiryDate,103) as EnquiryDate,Customer.CustomerName  from paymenthdr Inner Join paymentDtl on paymentDtl.PaymentHdrID=paymenthdr.PaymentHdrID Inner Join Enquiry on paymenthdr.EnquiryNo=Enquiry.EnquiryNo Inner Join Customer on Customer.CustomerID=paymenthdr.CustomerID Where paymenthdr.orderID Is Null And PaymentDtl.PaymentType=1 Order By EnquiryNo desc ";//,OD.OrderNo,Convert(Varchar(10),P.OrderDate,103) OrderDate
    Line 3078:
    Line 3079:            dtblPaymentDtl = objDbManip.GetDataTable(strSQL);
    Line 3080:
    Line 3081:            gvBrowser.KeyFieldName = "PaymentHdrID";
     
    
    Source File: c:\Documents and Settings\aftabm\Desktop\LloydsOMS\LloydsOMS\WebForms\main.aspx.cs    Line: 3079 

    Stack Trace:

    [Exception: System.Data.SqlClient.SqlException (0x80131904): Ambiguous column name 'EnquiryNo'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at LloydsDAL.DBManupulation.GetDataTable(String strSQL) in C:\lloyds\LloydsDAL\DBManipulation.cs:line 69]
       LloydsDAL.DBManupulation.GetDataTable(String strSQL) in C:\lloyds\LloydsDAL\DBManipulation.cs:74
       WebForms_main.LoadRetailSalesData() in c:\Documents and Settings\aftabm\Desktop\LloydsOMS\LloydsOMS\WebForms\main.aspx.cs:3079
       WebForms_main.nbMenu_ItemClick(Object source, NavBarItemEventArgs e) in c:\Documents and Settings\aftabm\Desktop\LloydsOMS\LloydsOMS\WebForms\main.aspx.cs:745
       DevExpress.Web.ASPxNavBar.ASPxNavBar.OnItemClick(NavBarItemEventArgs e) +96
       DevExpress.Web.ASPxNavBar.ASPxNavBar.RaisePostBackEvent(String eventArgument) +541
       DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
    
    Wednesday, June 8, 2011 5:32 AM

All replies

  • User2080995897 posted

    I m not an expert so plz guys explain things step by step plz :-(

    Wednesday, June 8, 2011 5:34 AM
  • User-366017857 posted

    It may be because of Incorrect Column Name EnquiryNo

    Check this column is exist in table or not or you provided incorrect column name

    Wednesday, June 8, 2011 5:38 AM
  • User2080995897 posted

    No i just copy and pasted the error so i have not provided any incorrect column name..... and the system i m working on is so complex that dont even have a clue where to start from :-(

    Wednesday, June 8, 2011 5:45 AM
  • User-366017857 posted

    Can you please provide the code so that it will be more clear

    Wednesday, June 8, 2011 5:49 AM
  • User2080995897 posted

    ok here is the code for DBManipulation.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.IO;
    using System.Web.Configuration;
    
    namespace LloydsDAL
    {
        public class DBManupulation : IDisposable
        {
            SqlConnection objSqlConnection = null;
            SqlDataAdapter objSqlDataAdapter = null;
            SqlTransaction objTran = null;
    
            string strConnection = WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    
            public void Dispose()
            {
                if (objSqlConnection != null) objSqlConnection.Dispose();
                if (objSqlDataAdapter != null) objSqlDataAdapter.Dispose();
                if (objTran != null) objTran.Dispose();
    
               
            }
    
            #region GetSingleValue
            public object GetSingleValue(string strSQL)
            {
                SqlCommand objSqlCommand = null;
                try
                {
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.Text;
    
                    return objSqlCommand.ExecuteScalar();
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
    
                    objSqlCommand.Dispose();
                }
            }
            #endregion
    
            #region GetDataSet
            public DataTable GetDataTable(string strSQL)
            {
                DataTable dt = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    dt = new DataTable();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.Text;
    
                    objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    objSqlDataAdapter.Fill(dt);
                    return dt;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
    
                }
                finally
                {
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                }
            }
            #endregion
    
            #region GetDataSet
            public DataSet GetDataSet(string strSQL)
            {
                DataSet dsTemp = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    dsTemp = new DataSet();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.Text;
    
                    objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    objSqlDataAdapter.Fill(dsTemp);
                    return dsTemp;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
    
                }
                finally
                {
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
    
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                }
            }
            #endregion
    
            #region Execute Non Query
            public void ExecNonQuery(string strSqlQuery)
            {
                SqlCommand objSqlCommand = null;
                try
                {
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strSqlQuery, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.Text;
                    objSqlCommand.ExecuteNonQuery();
                }
                catch (SqlException sqex)
                {
                    switch (sqex.Errors[0].Number)
                    {
                        case 547:
                            DataSet dsErrorData = new DataSet();
                            DataTable dtErrorData = new DataTable();
                            dtErrorData.Columns.Add("Message");
                            dtErrorData.Columns.Add("Table1");
                            dtErrorData.Columns.Add("Table2");
                            dtErrorData.Columns.Add("ConstraintName");
                            dsErrorData.Tables.Add(dtErrorData);
    
                            string strTemp1 = sqex.Errors[0].Message.Substring(sqex.Errors[0].Message.IndexOf('\"') + 1, sqex.Errors[0].Message.LastIndexOf('\"') - sqex.Errors[0].Message.IndexOf('\"') - 1);
                            string strTemp2 = strTemp1.Substring(0, strTemp1.IndexOf('\"'));
                            string[] strArr = strTemp2.Split('_');
    
                            dtErrorData.TableName = "ErrorData";
                            dtErrorData.Rows.Add(new object[] { "Foreign Key Violated", strArr[1], strArr[2], strTemp2 });
                            throw new Exception(dsErrorData.GetXml());
                        default:
                            throw new Exception(sqex.ToString());
                    }
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
    
                finally
                {
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                    objSqlCommand.Dispose();
                }
            }
            #endregion
    
            #region GetResultset
            public bool IsExists(string strStoreProcName, NameValuePairList objNameValuePairList)
            {
                SqlCommand objSqlCommand = null;
                try
                {
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
                    
                    foreach (NameValuePair objNVP in objNameValuePairList)
                    {
                        objSqlCommand.Parameters.AddWithValue(objNVP.Name, objNVP.Value);
                    }
                    objSqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                    objSqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
                    objSqlCommand.ExecuteNonQuery();
    
                    if((int)objSqlCommand.Parameters["@ReturnValue"].Value==0)
                        return true;
                    else
                        return false;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            #endregion
    
            #region GetResultset
            public DataSet GetResultset(string strStoreProcName, NameValuePairList objNameValuePairList)
            {
                DataSet dsTemp = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    dsTemp = new DataSet();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
    
                    foreach (NameValuePair objNVP in objNameValuePairList)
                    {
                        objSqlCommand.Parameters.AddWithValue(objNVP.Name, objNVP.Value);
                    }
    
                    objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    objSqlDataAdapter.Fill(dsTemp);
    
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                }
    
                return dsTemp;
            }
    
            public DataTable GetDataTableFromSp(string strStoreProcName, SqlParameter[] colParam)
            {
                DataTable dtTemp = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    dtTemp = new DataTable();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
    
                    foreach (SqlParameter param in colParam)
                    {
                        objSqlCommand.Parameters.Add(param);
                    }
    
                    objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    objSqlDataAdapter.Fill(dtTemp);
    
                    return dtTemp;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            #endregion
            #region GetSingleValueFromSp
            public object GetSingleValueFromSp(string strStoreProcName, SqlParameter[] colParam)
            {
                object objValue = null;
                DataTable dtTemp = null;
                SqlCommand objSqlCommand = null;
                try
               {
                   int intCount = 0;
                    dtTemp = new DataTable();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
    
                    foreach (SqlParameter param in colParam)
                    {
                        objSqlCommand.Parameters.Add(param);
                        objSqlCommand.Parameters[objSqlCommand.Parameters.Count - 1].Direction = ParameterDirection.Output;
                        intCount = objSqlCommand.Parameters.Count - 1;
                    }
                    
    
                    objSqlCommand.ExecuteNonQuery();
    
                    objValue = objSqlCommand.Parameters[intCount].Value;
                    return objValue;
                }
            
        
    
                    //objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    //objSqlDataAdapter.Fill(dtTemp);
    
                    //return dtTemp;
        
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            #endregion
    
            #region GetSingleOutputValueFromSp
            /// <summary>
            /// 20072009
            /// </summary>
            /// <param name="strStoreProcName"></param>
            /// <param name="colParam"></param>
            /// <returns></returns>
            public object GetSingleOutputValueFromSp(string strStoreProcName, SqlParameter[] colParam)
            {
                object objValue = null;
                DataTable dtTemp = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    int intCount = 0;
                    dtTemp = new DataTable();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
    
                    foreach (SqlParameter param in colParam)
                    {
                        objSqlCommand.Parameters.Add(param);
                        objSqlCommand.Parameters[objSqlCommand.Parameters.Count - 1].Direction = ParameterDirection.Input;
                        intCount = objSqlCommand.Parameters.Count - 1;
                    }
                    objSqlCommand.Parameters[intCount].Direction = ParameterDirection.InputOutput;
                        
    
                    objSqlCommand.ExecuteNonQuery();
    
                    objValue = objSqlCommand.Parameters[intCount].Value;
                    return objValue;
                }
    
    
    
                    //objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                //objSqlDataAdapter.Fill(dtTemp);
    
                    //return dtTemp;
    
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            #endregion
    
    
            #region GetResultsetReportMaster
            public DataSet GetResultsetReportMaster(string strStoreProcName)
            {
                DataSet dsTemp = null;
                SqlCommand objSqlCommand = null;
                try
                {
                    dsTemp = new DataSet();
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
    
                    /*foreach (NameValuePair objNVP in objNameValuePairList)
                    {
                        objSqlCommand.Parameters.AddWithValue(objNVP.Name, objNVP.Value);
                    }*/
    
                    objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                    objSqlDataAdapter.Fill(dsTemp);
    
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlDataAdapter.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
    
                return dsTemp;
    
            }
            #endregion
    
            #region ExecSP
            public void ExecSP(string strStoreProcName, NameValuePairList objNameValuePairList)
            {
                SqlCommand objSqlCommand = null;
                try
                {
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
                    foreach (NameValuePair objNVP in objNameValuePairList)
                    {
                        objSqlCommand.Parameters.AddWithValue(objNVP.Name, objNVP.Value);
                    }
    
                    objSqlCommand.ExecuteNonQuery();
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            public object ExecSPWithReturnValue(string strStoreProcName, NameValuePairList objNameValuePairList)
            {
                object objValue = null;
                SqlCommand objSqlCommand = null;
    
                try
                {
                    int intCount = 0;
    
    
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strStoreProcName, objSqlConnection);
                    objSqlCommand.CommandType = CommandType.StoredProcedure;
                    foreach (NameValuePair objNVP in objNameValuePairList)
                    {
                        objSqlCommand.Parameters.AddWithValue(objNVP.Name, objNVP.Value);
    
                        if (objNVP.ParaType)
                        {
                            objSqlCommand.Parameters[objSqlCommand.Parameters.Count - 1].Direction = ParameterDirection.Output;
                            intCount = objSqlCommand.Parameters.Count - 1;
                        }
                    }
    
                    objSqlCommand.ExecuteNonQuery();
    
                    objValue = objSqlCommand.Parameters[intCount].Value;
    
    
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
                return objValue;
            }
            #endregion
    
            #region Get Schema Of A Table
            public DataTable GetSchemaFromStoredProcOrTable(string strTableOrFunction)
            {
                DataTable dtRet = null;
                SqlCommand cmd = null;
                SqlDataReader Rd = null;
                SqlConnection con = null;
                try
                {
                    con = new SqlConnection(strConnection);
    
                    con.Open();
    
                    cmd = new SqlCommand("select * from " + strTableOrFunction, con);
                    Rd = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
    
                    dtRet=Rd.GetSchemaTable();
                    return dtRet;
                    
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    Rd.Close();
                    con.Close();
                }
            }
            #endregion
    
            #region GetTableSchema
    
            public DataSet GetTableSchema(string strSQL, string strTableName)
            {
                DataSet ds = new DataSet();
                System.Data.SqlClient.SqlDataAdapter da = null;
                System.Data.SqlClient.SqlConnection con = null;
                try
                {
                    con = new SqlConnection(strConnection);
                    da = new SqlDataAdapter(strSQL, con);
    
                    da.FillSchema(ds, SchemaType.Source, strTableName);
    
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        dc.AllowDBNull = true;
                    }
                    ds.Tables[0].Constraints.Clear();
                    
                    return ds;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                        con.Dispose();
                        con = null;
                    }
                    if (da != null)
                    {
                        da.Dispose();
                        da = null;
                    }
                }
            }
            #endregion
    
            #region GetTableSchema1
            public DataSet GetTableSchema1(string strSQL, string strTableName)
            {
                DataSet ds = new DataSet();
                DataSet ds1 = new DataSet();
                ds1.Tables.Add(new DataTable());
    
                System.Data.SqlClient.SqlConnection con = null;
                System.Data.SqlClient.SqlDataAdapter da = null;
     
                try
                {
                    con = new SqlConnection(strConnection);
                    da = new SqlDataAdapter(strSQL, con);
    
                    da.FillSchema(ds, SchemaType.Source, strTableName);
    
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        if (ds.Tables[0].Columns[i].DataType != typeof(bool))
                        {
                            ds1.Tables[0].Columns.Add(ds.Tables[0].Columns[i].ColumnName);
                            ds1.Tables[0].Columns[i].AllowDBNull = true;
                        }
                        else
                        {
                            ds1.Tables[0].Columns.Add(ds.Tables[0].Columns[i].ColumnName, typeof(bool));
                        }
                    }
    
                    ds1.Tables[0].Constraints.Clear();
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                    da.Dispose();
                    da = null;
                    ds.Dispose();
                }
    
                return ds1;
            }
            #endregion
    
            #region ExecuteSQL (Report)
            public DataSet ExecuteSQL(string strSQL, string strTableName)
            {
               
                DataSet ds = new DataSet();
    
                System.Data.SqlClient.SqlConnection con = new SqlConnection(strConnection);
    
                System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
                da.Fill(ds, strTableName);
                da.Dispose();
                con.Close();
                con.Dispose();
    
                return ds;
            }
            #endregion
    
            #region ExecuteScalar
            public object ExecuteScalar(string strSQL)
            {
                SqlCommand objSqlCommand = null;
                try
                {
                    objSqlConnection = new SqlConnection(strConnection);
                    objSqlConnection.Open();
                    objSqlCommand = new SqlCommand(strSQL, objSqlConnection);
    
                    object o = objSqlCommand.ExecuteScalar();
                    return o;
                }
                finally
                {
                    objSqlCommand.Dispose();
                    objSqlConnection.Close();
                    objSqlConnection.Dispose();
                }
            }
            #endregion
    
        }
    }
    
    Wednesday, June 8, 2011 6:03 AM
  • User2080995897 posted

    and this is the code for main.aspx.cs

     

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using DevExpress.Web.ASPxNavBar;
    using System.Web.Configuration;
    using DevExpress.Web.ASPxGridView;
    using System.IO;
    using LloydsDAL;
    using EeekSoft.Web;
    using System.Xml;
    using System.Web.Configuration;
    using System.Text;
    
    
    
    public partial class WebForms_main : System.Web.UI.Page, ICallbackEventHandler
    {
        string strConnection = WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        DBManupulation objDbManip = new DBManupulation();
        protected string returnValue = "";
        protected string xmlmenufile = string.Empty;
        DataTable dt = null;
        GridviewToExcel.export2Excel d = null;
        protected string strGenerateJobUrl = string.Empty;
    
    
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
            }
    
            Session.Timeout = 120;
            if (Session["UserID"] == null)
            {
                Response.Redirect("../frmLogin.aspx", false);
                Response.End();
            }
    
            xmlmenufile = Session["UserID"].ToString() + ".xml";
            xmlmenufile = Generic.Path + xmlmenufile;
            XmlDataSource1.DataFile = xmlmenufile;
    
            if (Generic.UserName.ToString().Trim() != "")
                lblUserName.Text = Generic.UserName.ToString().Trim();
    
            lblDate.Text = System.DateTime.Now.ToString("dd/MM/yyyy");
    
            ClientScriptManager cm = Page.ClientScript;
    
            string cbReference = cm.GetCallbackEventReference(this, "arg",
                "ReceiveServerData", "");
            String callbackScript = "function CallServer(arg, context) {" +
                cbReference + "; }";
            cm.RegisterClientScriptBlock(this.GetType(),
                "CallServer", callbackScript, true);
        }
    
        public void RaiseCallbackEvent(String eventArgument)
        {
            if (eventArgument != null)
            {
                
                string[] s = eventArgument.Split(':');
                if (s[0].ToString() != "-1")
                    OpenRetailOrderWindow(s[0], s[1]);
                else
                    Response.End();
            }
        }
    
        public string GetCallbackResult()
        {
            if (strGenerateJobUrl == "")
                Response.End();
            return strGenerateJobUrl;
        }
    
        protected string GetPermission(string strCaption)
        {
            string strPermission = string.Empty;
    
            //Access Rights
            SqlParameter sqlparam = new SqlParameter("@AccessGroupID", SqlDbType.Int);
            //sqlparam.Value = Generic.AccessGroupID;
            sqlparam.Value = Session["UserId"];  //21072009
            SqlParameter sqlparam1 = new SqlParameter("@caption", SqlDbType.VarChar);
            sqlparam1.Value = strCaption;
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = sqlparam;
            arrParam[1] = sqlparam1;
            dt = null;
            dt = objDbManip.GetDataTableFromSp("GetPermission", arrParam);
            if (dt.Rows.Count > 0)
                strPermission = dt.Rows[0][0].ToString() + "," + dt.Rows[0][1].ToString() + "," + dt.Rows[0][2].ToString() + "," + dt.Rows[0][3].ToString();
    
            if (dt != null) dt.Dispose();
            return strPermission;
        }
    
        protected override void LoadViewState(object savedState)
        {
            string ctrlname = Page.Request.Params["__EVENTTARGET"];
    
            if (ctrlname.ToUpper().Trim() == "NBMENU") return;
    
            base.LoadViewState(savedState);
            if (ViewState["mode"] != null)
            {
                if (ViewState["mode"].ToString() == "Customer")
                {
                    LoadCustomerData();
                }
                else if (ViewState["mode"].ToString() == "Company")
                {
                    LoadCompanyData();
                }
                else if (ViewState["mode"].ToString() == "Access Group")
                {
                    LoadAccessGroupData();
                }
                else if (ViewState["mode"].ToString() == "Access Rights")
                {
                    //LoadAccessRightData(); 
                    LoadLoginUserData();  //15072009
                }
                else if (ViewState["mode"].ToString() == "Login Users")
                {
                    LoadLoginUserData();
                }
                else if (ViewState["mode"].ToString() == "Product Data")
                {
                    LoadProductData();
                }
                else if (ViewState["mode"].ToString() == "Designer,Sales Persons and Fitter")
                {
                    LoadPersonnelData();
                }
                else if (ViewState["mode"].ToString() == "Process")
                {
                    LoadProcessData();
                }
                else if (ViewState["mode"].ToString() == "Concessions")
                {
                    LoadConcessionData();
                }
                else if (ViewState["mode"].ToString() == "Finance Company")
                {
                    LoadFinanceCompanyData();
                }
                else if (ViewState["mode"].ToString() == "Media")
                {
                    LoadMediaData();
                }
                else if (ViewState["mode"].ToString() == "Materials")
                {
                    LoadMaterialsData();
                }
                else if (ViewState["mode"].ToString() == "Manufacturer")
                {
                    LoadManufacturerData();
                }
                else if (ViewState["mode"].ToString() == "Letter Template")
                {
                    LoadLetterTemplateData();
                }
    
                else if (ViewState["mode"].ToString() == "Enquiry")
                {
                    LoadEnquiryData();
                }
                else if (ViewState["mode"].ToString() == "Sales Quotation")
                {
                    LoadSalesQuotationData();
                }
                else if (ViewState["mode"].ToString() == "Retail Sales")
                {
                    LoadRetailSalesData();
                }
                else if (ViewState["mode"].ToString() == "Generate Trade Order")
                {
                    LoadGenerateTradeOrderData();
                }
                else if (ViewState["mode"].ToString() == "Generate Retail Order")
                {
                    LoadGenerateRetailOrderData();
                }
                else if (ViewState["mode"].ToString() == "Production Advice")
                {
                    LoadProductionAdviceData();
                }
                else if (ViewState["mode"].ToString() == "Confirm Production Completion")
                {
                    LoadProductionCompletionData();
                }
                else if (ViewState["mode"].ToString() == "Generate Trade Invoice")
                {
                    LoadGenerateTradeInvoiceData();
                }
                else if (ViewState["mode"].ToString() == "Generate Retail Invoice")
                {
                    LoadGenerateRetailInvoiceData();
                }
                else if (ViewState["mode"].ToString() == "Despatch Job")
                {
                    LoadDespatchJob();
                }
                else if (ViewState["mode"].ToString() == "Delivery Job")
                {
                    LoadDeliveryJob();
                }
                else if (ViewState["mode"].ToString() == "Update Fitter Sheet")
                {
                    LoadUpdateFitterSheetData();
                }
                else if (ViewState["mode"].ToString() == "Warranty Certificate")
                {
                    LoadWarrantyCertificateData();
                }
                else if (ViewState["mode"].ToString() == "Order Closure")
                {
                    LoadOrderClosureData();
                }
                else if (ViewState["mode"].ToString() == "Records Advertisement Detail")
                {
                    LoadAdvertisementDtlData();
                }
                else if (ViewState["mode"].ToString() == "Make Payment to Media")
                {
                    LoadMediaDtlData();
                }
                else if (ViewState["mode"].ToString() == "Record Confirmation of Payment")
                {
                    LoadPaymentConfirmationDtlData();
                }
                else if (ViewState["mode"].ToString() == "Calculate Commission and Wages")
                {
                    LoadCalculateCommissionWagesDtlData();
                }
                else if (ViewState["mode"].ToString() == "Manage Absent")
                {
                    LoadManageAbsentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Receipt")
                {
                    LoadPaymentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment")
                {
                    LoadReceiptDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment To Designer")
                {
                    LoadDesignerPaymentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment To Fitter")
                {
                    LoadFitterPaymentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Budget")
                {
                    LoadBudgetData();
                }
                else if (ViewState["mode"].ToString() == "Payment To Concession")
                {
                    LoadConcessionPaymentDtlData();
                }
                gvBrowser.ClientSideEvents.RowDblClick = "ShowDialogEditMode";
            }
        }
    
        protected void CreateDataColumn(
            ASPxGridView _gv,
            string _caption,
            string _fieldname,
            int _width,
            bool _readonly,
            bool _visible)
        {
            GridViewDataColumn col = new GridViewDataColumn();
            col.Caption = _caption;
            col.FieldName = _fieldname;
            col.Width = _width;
            col.ReadOnly = _readonly;
            col.Visible = _visible;
            if (!_visible)
                col.VisibleIndex = -1;
            else
                col.VisibleIndex = _gv.VisibleColumns.Count - 1;
    
            _gv.Columns.Add(col);
        }
    
        protected void nbMenu_ItemClick(object source, DevExpress.Web.ASPxNavBar.NavBarItemEventArgs e)
        {
            string s = string.Empty;
            lblCaption.Visible = false;  //12072009
    
            NavBarItem objItem = null;
            objItem = e.Item;
            s = objItem.Text;
    
            if (ViewState["mode"] != null)
            {
                ViewState.Remove("mode");
            }
            ViewState.Add("mode", s);
    
            Common.SaveFlag = false;
    
            if (s.ToUpper() == "BUDGET")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Budget" + "," + GetPermission("Budget");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Budget";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnCustomerNote.Visible = false;
                btnExport.Visible = false;
    
                LoadBudgetData();
            }
    
            if (s.ToUpper() == "CUSTOMER")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Customer" + "," + GetPermission("Customer");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Customer";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnCustomerNote.Visible = true;
                btnExport.Visible = false;
    
                LoadCustomerData();
            }
            else if (s.ToUpper() == "COMPANY")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Company" + "," + GetPermission("Company");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Company";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadCompanyData();
            }
            else if (s.ToUpper() == "ACCESS GROUP")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Access Group" + "," + GetPermission("Access Group");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Access Group";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadAccessGroupData();
            }
            else if (s.ToUpper() == "ACCESS RIGHTS")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Access Rights" + "," + GetPermission("Access Rights");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Access Right";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;  //ch true  //15072009
                btnEdit.Visible = true;
                btnDelete.Visible = false;  //ch true  //15072009
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                //LoadAccessRightData();
                LoadLoginUserData();  //15072009
            }
            else if (s.ToUpper() == "LOGIN USERS")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Login Users" + "," + GetPermission("Login Users");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Login User";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadLoginUserData();
            }
            else if (s.ToUpper() == "PRODUCT DATA")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Product Data" + "," + GetPermission("Product Data");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Product Data";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadProductData();
            }
            else if (s.ToUpper() == "DESIGNER,SALES PERSONS AND FITTER")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Designer Sales Persons and Fitter" + "," + GetPermission("Designer,Sales Persons and Fitter");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Designer,Sales Persons and Fitter";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadPersonnelData();
            }
            else if (s.ToUpper() == "PROCESS")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Process" + "," + GetPermission("Process");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Process";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadProcessData();
            }
            else if (s.ToUpper() == "CONCESSIONS")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Concessions" + "," + GetPermission("Concessions");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Concession";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadConcessionData();
            }
            else if (s.ToUpper() == "FINANCE COMPANY")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Finance Company" + "," + GetPermission("Finance Company");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Finance";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadFinanceCompanyData();
            }
            else if (s.ToUpper() == "MEDIA")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Media" + "," + GetPermission("Media");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Media";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadMediaData();
            }
            else if (s.ToUpper() == "MATERIALS")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Materials" + "," + GetPermission("Materials");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Material";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadMaterialsData();
            }
            else if (s.ToUpper() == "MANUFACTURER")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Manufacturer" + "," + GetPermission("Manufacturer");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Manufacturer";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadManufacturerData();
            }
            else if (s.ToUpper() == "LETTER TEMPLATE")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Letter Template" + "," + GetPermission("Letter Template");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Letter Template";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadLetterTemplateData();
            }
    
            else if (s.ToUpper() == "LOOKUP")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Lookup" + "," + GetPermission("Lookup");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Lookup";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnDelete.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadLookUpData();
            }
            else if (s.ToUpper() == "ENQUIRY")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Enquiry" + "," + GetPermission("Enquiry");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Enquiry";
                btnAdvance.Visible = true;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadEnquiryData();
            }
            else if (s.ToUpper() == "SALES QUOTATION")
            {
                Session.Timeout = 120;
                Session["OType"] = "T";
    
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Sales Quotation" + "," + GetPermission("Sales Quotation");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Sales Quotation";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = true;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadSalesQuotationData();
            }
            else if (s.ToUpper() == "RETAIL SALES")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Retail Sales" + "," + GetPermission("Retail Sales");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Retail Sales";
                btnAdvance.Visible = false;
                btnOrder.Visible = true;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = false;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadRetailSalesData();
            }
            else if (s.ToUpper() == "GENERATE TRADE ORDER")
            {
                Session.Timeout = 120;
                Session["OType"] = "T";
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Generate Trade Order" + "," + GetPermission("Generate Trade Order");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Generate Trade Order";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = true;
    
                LoadGenerateTradeOrderData();
            }
            else if (s.ToUpper() == "GENERATE RETAIL ORDER")
            {
                Session.Timeout = 120;
                Session["OType"] = "R";
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Generate Retail Order" + "," + GetPermission("Generate Retail Order");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Generate Retail Order";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = true;
    
                LoadGenerateRetailOrderData();
            }
            else if (s.ToUpper() == "PRODUCTION ADVICE")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Production Advice" + "," + GetPermission("Production Advice");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Production Advice";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadProductionAdviceData();
            }
            else if (s.ToUpper() == "CONFIRM PRODUCTION COMPLETION")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Confirm Production Completion" + "," + GetPermission("Confirm Production Completion");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Confirm Production Completion";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadProductionCompletionData();
            }
            else if (s.ToUpper() == "GENERATE TRADE INVOICE")
            {
                Common.InvoiceMode = "T";
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Generate Trade Invoice" + "," + GetPermission("Generate Trade Invoice");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Generate Trade Invoice";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = false;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadGenerateTradeInvoiceData();
            }
            else if (s.ToUpper() == "GENERATE RETAIL INVOICE")
            {
                Common.InvoiceMode = "R";
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Generate Retail Invoice" + "," + GetPermission("Generate Retail Invoice");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Generate Retail Invoice";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = false;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadGenerateRetailInvoiceData();
            }
            else if (s.ToUpper() == "DESPATCH JOB")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Despatch Job" + "," + GetPermission("Despatch Job");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Despatch Job";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = false;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadDespatchJob();
            }
            else if (s.ToUpper() == "DELIVERY JOB")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Delivery Job" + "," + GetPermission("Delivery Job");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Delivery Job";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = false;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadDeliveryJob();
            }
            else if (s.ToUpper() == "UPDATE FITTER SHEET")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Update Fitter Sheet" + "," + GetPermission("Update Fitter Sheet");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Update Fitter Sheet";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadUpdateFitterSheetData();
            }
            else if (s.ToUpper() == "WARRANTY CERTIFICATE")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Warranty Certificate" + "," + GetPermission("Warranty Certificate");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Warranty Certificate";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadWarrantyCertificateData();
            }
            else if (s.ToUpper() == "ORDER CLOSURE")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Order Closure" + "," + GetPermission("Order Closure");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Order Closure";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = false;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadOrderClosureData();
            }
            else if (s.ToUpper() == "RECORDS ADVERTISEMENT DETAIL")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Records Advertisement Detail" + "," + GetPermission("Records Advertisement Detail");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Records Advertisement Detail";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadAdvertisementDtlData();
            }
            else if (s.ToUpper() == "MAKE PAYMENT TO MEDIA")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Make Payment to Media" + "," + GetPermission("Make Payment to Media");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Make Payment to Media";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadMediaDtlData();
            }
    
            else if (s.ToUpper() == "RECORD CONFIRMATION OF PAYMENT")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Record Confirmation of Payment" + "," + GetPermission("Record Confirmation of Payment");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Record Confirmation of Payment";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadPaymentConfirmationDtlData();
            }
    
            else if (s.ToUpper() == "CALCULATE COMMISSION AND WAGES")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Calculate Commission and Wages" + "," + GetPermission("Calculate Commission and Wages");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Calculate Commission and Wages";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadCalculateCommissionWagesDtlData();
            }
    
            else if (s.ToUpper() == "MANAGE ABSENT")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Manage Absent" + "," + GetPermission("Manage Absent"); ;
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Manage Absent";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadManageAbsentDtlData();
            }
    
            else if (s.ToUpper() == "RECEIPT")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Receipt" + "," + GetPermission("Receipt");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Receipt";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = false;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadPaymentDtlData();
            }
    
            else if (s.ToUpper() == "PAYMENT")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Payment" + "," + GetPermission("Payment"); ;
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Payment";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = true;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadReceiptDtlData();
            }
    
            else if (s.ToUpper() == "PAYMENT TO DESIGNER")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Payment To Designer" + "," + GetPermission("Payment To Designer");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Payment To Designer";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadDesignerPaymentDtlData();
            }
            else if (s.ToUpper() == "PAYMENT TO FITTER")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Payment To Fitter" + "," + GetPermission("Payment To Fitter");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Payment To Fitter";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
    
                LoadFitterPaymentDtlData();
            }
            else if (s.ToUpper() == "PAYMENT TO CONCESSION")
            {
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Payment To Concession" + "," + GetPermission("Payment To Concession");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Payment To Concession";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
                btnExport.Visible = false;
    
                LoadConcessionPaymentDtlData();
            }
                //12072009
            else if (s.ToUpper() == "ORDER DATA")
            {
    
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
               
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "Order Data" + "," + GetPermission("Order Data");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "Export to XML";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
                btnExport.Visible = false;
    
               //Response.Redirect("../Export/Export2xml.aspx");
                Export_to_XML(1);
            }
           
                //12072009
    
            //13072009
            else if (s.ToUpper() == "DAT FILE")
            {
    
                if (Response.Cookies["menucaption"] != null)
                {
                    Response.Cookies.Remove("menucaption");
                }
    
                HttpCookie cookie = new HttpCookie("menucaption");
                cookie["menucaption"] = "DAT File" + "," + GetPermission("DAT File");
                Response.Cookies.Add(cookie);
                lblCaption.Text = "DAT File";
                btnAdvance.Visible = false;
                btnOrder.Visible = false;
                btnCustomerNote.Visible = false;
                btnConvert.Visible = false;
                btnAdd.Visible = false;
                btnEdit.Visible = true;
                btnDelete.Visible = true;
                btnRefresh.Visible = true;
                btnExport.Visible = false;
                btnExport.Visible = false;
    
                //Response.Redirect("../Export/Export2xml.aspx");
                Export_to_DAT(1);
            }
           //13072009
    
            else if (s.ToUpper() == "DESIGNER")
            {
                Response.Redirect("../Scheduler/frmDesignerBrw.aspx");
            }
    
            gvBrowser.ClientSideEvents.RowDblClick = "ShowDialogEditMode";
        }
    
        /// <summary>
        /// 12072009
        /// </summary>
         protected void Export_to_XML(int ordID)
            {
                DataTable dtblProductUser = null;
                    try
                    {
                        /*
                     string strSQL = "SELECT     jd.JobOrderDetailsID, j.JobOrderID, j.OrderID, j.QuotationID, j.JobNo, j.JobDate, j.DeliveryType, j.DeliveryDate, j.StartDate, j.JobReference, j.RangeID, ";
                     strSQL += "              j.RouteStyleID, j.CarcassMaterialID, j.PanelMaterialID, j.HingeTypeID, j.EdgeBandID1, j.EdgeBandID2, j.EdgeBandID3, j.BoardBackID, j.JobValue, ";
                     strSQL += "              j.DeliveryChg, j.VATPcnt, j.VATAmt, j.DiscPcnt, j.DiscAmt, j.StatusID, j.Notes, j.GrandTotal, j.DespatchDate, ";
                     strSQL += "              jd.SerialNo, jd.JobOrderID AS Expr1, jd.ProductID, jd.ProductPackageID, jd.ProductName, jd.IsParent, jd.ParentSerialNo, jd.ProductGroupID, jd.Height, ";
                      strSQL += "             jd.Width, jd.Depth, jd.LH, jd.RH, jd.Quantity, jd.SCPcnt, jd.Premium, jd.Price, jd.AddedBy, ";
                      strSQL += "               dbo.getHingeType(j.HingeTypeID) as HingeTypeName, rs.RoutStyleName, p.ProductCode ";
                      strSQL += " FROM         dbo.RoutStyle AS rs INNER JOIN ";
                     strSQL += "              dbo.JobOrder AS j ON rs.RoutStyleID = j.RouteStyleID INNER JOIN ";
                     strSQL += "               dbo.Product AS p INNER JOIN ";
                     strSQL += "              dbo.JobOrderDetails AS jd ON p.ProductID = jd.ProductID ON j.JobOrderID = jd.JobOrderID ";
                    strSQL += " order by P.ProductCode";
                        */
    
                    //20072009
                    bool bPen = false;
                    bool bTCut = false;
                    string str1 = "";
                    DataRow dr1;
                    string strSQL = "     SELECT V.* , dbo.Get_HingeType(V.JobOrderID, V.ProductID) AS HingeTypeName FROM vw_jobOrdDet V";
                    strSQL += "             WHERE     (ProductType = 'Panel') ";
                    strSQL += "            AND  OrderID =" + ordID;
                    strSQL += "           ORDER BY V.ProductCode";
                    string xmlFile = "";  //24072009
                    string pcode = "";  //05082009
                    int ctr = 0; //05082009
    
                    dtblProductUser = objDbManip.GetDataTable(strSQL);
                    XmlDocument XDoc = new XmlDocument();
                    XmlElement XElemRoot;
                    XmlElement Xsource;
                    XmlElement XTemp;
                    XElemRoot = XDoc.CreateElement("parameters");
                    XDoc.AppendChild(XElemRoot);
    
                   
                   
                    if (dtblProductUser.Rows.Count > 0)
                    {
                        pcode = dtblProductUser.Rows[0]["ProductCode"].ToString();    
                        
                        while (ctr < dtblProductUser.Rows.Count)  
                        {
                            DataRow dr = dtblProductUser.Rows[ctr]; 
                            
                            if (dr["ProductCode"].ToString() != pcode)  
                            {
                                if (xmlFile.Length > 0)  
                                {
                                    string exportDir = ConfigurationManager.AppSettings["JobXMLPath"].ToString(); 
    
                                    if (!Directory.Exists(exportDir))
                                        Directory.CreateDirectory(exportDir);
    
                                    xmlFile = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim() + "_" + pcode + ".xml";  
    
                                    String fullPath = "mmm\\" + exportDir + "\\" + xmlFile;
                                    int pos = Convert.ToInt16(fullPath.LastIndexOf("\\"));
                                    int fileLen = fullPath.Length - pos;
                                    string flname = fullPath.Substring(pos + 1, fileLen - 1);
                                    XDoc.Save(exportDir + "\\" + flname);
                                }
                                XDoc = new XmlDocument();
                               
                                XElemRoot = XDoc.CreateElement("parameters");
                                XDoc.AppendChild(XElemRoot);
    
                            }
                           
                            Xsource = XDoc.CreateElement("PanelParameters");
                            XElemRoot.AppendChild(Xsource);
    
                            XTemp = XDoc.CreateElement("productCode");
                            XTemp.InnerText = dr["ProductCode"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("description");
                            XTemp.InnerText = dr["ProductName"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("partNumber");
                            XTemp.InnerText = dr["SerialNo"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("height");
                            XTemp.InnerText = dr["height"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("width");
                            XTemp.InnerText = dr["width"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("routStyle");
                            XTemp.InnerText = dr["routStyleName"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            ///////////////////05082009
                            //XTemp = XDoc.CreateElement("mappingParameters");
                            //Xsource.AppendChild(XTemp);
    
                            //XmlElement XTemp1 = XDoc.CreateElement("hingeStyle");
                            //XTemp1.InnerText = dr["HingeTypeName"].ToString();
                            //XTemp.AppendChild(XTemp1);
    
                            //XTemp1 = XDoc.CreateElement("jobID");
                            //XTemp1.InnerText = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim();
                            //XTemp.AppendChild(XTemp1);
    
                            XTemp = XDoc.CreateElement("hingeStyle");
                            XTemp.InnerText = dr["HingeTypeName"].ToString();
                            Xsource.AppendChild(XTemp);
    
                            XTemp = XDoc.CreateElement("jobID");
                            XTemp.InnerText = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim();
                            Xsource.AppendChild(XTemp);
    
                            ///////////////////05082009
    
                            str1 = "SELECT * FROM PanelConfig WHERE  ProductId=" + dr["ProductID"].ToString().Trim();
                            DataTable dtPanel = objDbManip.GetDataTable(str1);
                            if (dtPanel.Rows.Count > 0)
                            {
    
                                dr1 = dtPanel.Rows[0];
                                bPen = Convert.ToBoolean(dr1["PenNumbering"]);
                                bTCut = Convert.ToBoolean(dr1["TrimCut"]);
                            }
                            else
                            {
                                bPen = true;
                                bTCut = true;
                            }
    
                            Xsource = XDoc.CreateElement("penNumberingParameters");
                            XElemRoot.AppendChild(Xsource);
    
                            XTemp = XDoc.CreateElement("enabled");
                            XTemp.InnerText = bPen.ToString();
                            Xsource.AppendChild(XTemp);
    
                            Xsource = XDoc.CreateElement("trimCutParameters");
                            XElemRoot.AppendChild(Xsource);
    
                            XTemp = XDoc.CreateElement("enabled");
                            XTemp.InnerText = bTCut.ToString();
                            Xsource.AppendChild(XTemp);
    
                            pcode = dr["ProductCode"].ToString();
                            xmlFile = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim() + "_" + pcode + ".xml";
                            ctr = ctr + 1; 
                        }
                        if (xmlFile.Length > 0)
                        {
                            string exportDir = ConfigurationManager.AppSettings["JobXMLPath"].ToString();
    
                            if (!Directory.Exists(exportDir))
                                Directory.CreateDirectory(exportDir);
    
                            
                            String fullPath = "mmm\\" + exportDir + "\\" + xmlFile;
                            int pos = Convert.ToInt16(fullPath.LastIndexOf("\\"));
                            int fileLen = fullPath.Length - pos;
                            string flname = fullPath.Substring(pos + 1, fileLen - 1);
                            XDoc.Save(exportDir + "\\" + flname);
                        }
                        
                    }
                    //lblCaption.Text = "Data successfully exported to xml";
                    //lblInfo.Visible = true;  
                }
                catch(System.Exception  ex )
                {
                    throw ex;
                    //lblInfo.Text = "";
                }
                finally
                {
                    //if (dtblProductUser != null) dtblProductUser.Dispose();
                }
            }
    
    
            /// <summary>
            /// 13072009
            /// </summary>
        protected void Export_to_DAT(int ordID)
            {
                DataTable dtblProductUser = null;
                string pcode = "";  //05082009
                int ctr = 0; //05082009
                try
                {
                    /*
                    string strSQL = "SELECT     jd.JobOrderDetailsID, j.JobOrderID, j.OrderID, j.QuotationID, j.JobNo, j.JobDate, j.DeliveryType, j.DeliveryDate, j.StartDate, j.JobReference, j.RangeID, ";
                    strSQL += "              j.RouteStyleID, j.CarcassMaterialID, j.PanelMaterialID, j.HingeTypeID, j.EdgeBandID1, j.EdgeBandID2, j.EdgeBandID3, j.BoardBackID, j.JobValue, ";
                    strSQL += "              j.DeliveryChg, j.VATPcnt, j.VATAmt, j.DiscPcnt, j.DiscAmt, j.StatusID, j.Notes, j.GrandTotal, j.DespatchDate, ";
                    strSQL += "              jd.SerialNo, jd.JobOrderID AS Expr1, jd.ProductID, jd.ProductPackageID, jd.ProductName, jd.IsParent, jd.ParentSerialNo, jd.ProductGroupID, jd.Height, ";
                    strSQL += "             jd.Width, jd.Depth, jd.LH, jd.RH, jd.Quantity, jd.SCPcnt, jd.Premium, jd.Price, jd.AddedBy, ";
                    strSQL += "               dbo.getHingeType(j.HingeTypeID) as HingeTypeName, rs.RoutStyleName, p.ProductCode ";
                    strSQL += " FROM         dbo.RoutStyle AS rs INNER JOIN ";
                    strSQL += "              dbo.JobOrder AS j ON rs.RoutStyleID = j.RouteStyleID INNER JOIN ";
                    strSQL += "               dbo.Product AS p INNER JOIN ";
                    strSQL += "              dbo.JobOrderDetails AS jd ON p.ProductID = jd.ProductID ON j.JobOrderID = jd.JobOrderID ";
                    strSQL += "  WHERE  j.OrderID =" + ordID;
                    strSQL += " order by P.ProductCode";
                    */
    
    
                    //21072009
                    string strSQL = "     SELECT V.* , dbo.Get_HingeType(V.JobOrderID, V.ProductID) AS HingeTypeName FROM vw_jobOrdDet V";
                    strSQL += "             WHERE     (ProductType = 'Panel') ";
                    strSQL += "            AND  OrderID =" + ordID;
                    strSQL += "           ORDER BY V.ProductCode";
    
    
    
                    StreamWriter sw = new StreamWriter("a.dat"); 
                    
    
    
                    dtblProductUser = objDbManip.GetDataTable(strSQL);
                    
    
                    string datFile = ".";
                    string exportDir =  "ExportedXML";  //ConfigurationManager.AppSettings["JobXMLPath"].ToString();
                    //exportDir = Request.MapPath("..\\") + exportDir;
    
                    exportDir = ConfigurationManager.AppSettings["JobXMLPath"].ToString();  //21072009
    
                    if (!Directory.Exists(exportDir))
                        Directory.CreateDirectory(exportDir);
    
                    
    
    
                    //////////////////////
    
                    if (dtblProductUser.Rows.Count > 0)
                    {
                        //pcode = dtblProductUser.Rows[0]["ProductCode"].ToString();    
                        int ctr1 = 0;
                        //foreach (DataRow dr in dtblProductUser.Rows)
                        while (ctr < dtblProductUser.Rows.Count)   
                        {
    
                            DataRow dr = dtblProductUser.Rows[ctr]; 
                            
    
                            //datFile = "JobItem" + "_1.DAT"  ;
    
                            //datFile = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim() +".DAT";  //21072009
                            datFile = dr["OrderID"].ToString().Trim() + "_" + dr["JobNo"].ToString().Trim() + "_" + dr["ProductCode"].ToString().Trim() + ".DAT";  //05082009 
    
                            if (dr["ProductCode"].ToString() != pcode)  
                            {
                                if (sw != null)
                                {
                                    sw.Close();
                                }
                                ctr1 = 0; 
    
                               
                                String fullPath = "mmm\\" + exportDir + "\\" + datFile;
                                int pos = Convert.ToInt16(fullPath.LastIndexOf("\\"));
                                int fileLen = fullPath.Length - pos;
                                string flname = fullPath.Substring(pos + 1, fileLen - 1);
                                string DatPath = exportDir + "\\" + datFile;
                                FileInfo fi = new FileInfo(DatPath);
                                
    
                                //FileInfo fi = new FileInfo(exportDir + "\\" + datFile);
                                //if (!fi.Exists)
                                {
                                    sw = new StreamWriter(DatPath);
                                }
    
                            }
                                ctr1++; 
                                sw.Write(ctr1.ToString().Trim()); 
                                sw.Write(",");
                                sw.Write(dr["ProductCode"].ToString());  //door code generated from  AlphaCam  //panel code
                                sw.Write(",");
                                sw.Write(dr["Quantity"].ToString());
                                sw.WriteLine("");
                            //}
                            pcode = dr["ProductCode"].ToString(); 
                            ctr = ctr + 1; 
                        }
    
                        sw.Close();
                    }
    
    
                    //lblCaption.Text = "Data successfully exported to DAT";
                    //lblInfo.Visible = true;
                }
                catch (System.Exception ex)
                {
                    throw ex;
                    //lblInfo.Text = "";
                }
                finally
                {
                    //if (dtblProductUser != null) dtblProductUser.Dispose();
                    
                }
            }
    
    
        protected void LoadBudgetData()
        {
            DataTable dtbl = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "BudgetID", "BudgetID", 1, true, false);
                CreateDataColumn(gvBrowser, "Budget Type", "BudgetTypeID", 50, true, true);
                CreateDataColumn(gvBrowser, "Month Name", "MonthName", 400, true, true);
                CreateDataColumn(gvBrowser, "Star Date", "StartDate", 50, true, true);
                CreateDataColumn(gvBrowser, "End Date", "EndDate", 50, true, true);
                CreateDataColumn(gvBrowser, "Display Week", "WeekNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Budget Amount", "BudgetAmount", 100, true, true);
    
                string strSQL = "SELECT BudgetID,case BudgetTypeID when 700 then 'Trade' when 701 then 'Retail' else Concession.ConcessionName end BudgetTypeID,MonthName,Convert(varchar(10),StartDate,103) as StartDate,Convert(varchar(10),EndDate,103) as EndDate,WeekNo,BudgetAmount FROM Budget Left Join Concession on Concession.ConcessionID=Budget.BudgetTypeID order by budgetid desc";
    
                dtbl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "BudgetID";
                gvBrowser.DataSource = (DataTable)dtbl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
    
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtbl != null) dtbl.Dispose();
            }
        }
    
        protected void LoadCompanyData()
        {
            DataTable dtblCompany = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "CompanyInfoID", "CompanyInfoID", 1, true, false);
                CreateDataColumn(gvBrowser, "Company Name", "CompanyName", 500, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
                CreateDataColumn(gvBrowser, "Mobile No.", "MobileNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Phone No.", "PhoneNo", 100, true, true);
                CreateDataColumn(gvBrowser, "E-Mail", "email", 200, true, true);
    
                string strSQL = "SELECT distinct CI.CompanyInfoID,CI.CompanyName,CI.CityName,CI.MobileNo,CI.PhoneNo,CI.email FROM CompanyInfo CI ";
                strSQL += "ORDER BY CI.CompanyInfoID desc ";
    
                dtblCompany = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "CompanyInfoID";
                gvBrowser.DataSource = (DataTable)dtblCompany;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblCompany != null) dtblCompany.Dispose();
            }
        }
    
        protected void LoadCustomerData()
        {
             DataTable dtCustomer = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "CustomerID", "CustomerID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer Code", "CustomerCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 500, true, true);
                CreateDataColumn(gvBrowser, "Type", "CustomerType", 100, true, true);
                CreateDataColumn(gvBrowser, "Mobile No.", "MobileNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Office No.", "OffPhoneNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Home No.", "Resphoneno", 100, true, true);
    
                string strSQL = "SELECT Cst.Note,Cst.CustomerCode,Case when Cst.CustomerType='T' then 'Trade' else 'Retail' end CustomerType,Cst.CustomerID,Cst.CustomerName,c.MobileNo,c.OffPhoneNo,c.Resphoneno,Cst.CityName FROM Customer Cst left join contact c on (c.entityid=Cst.customerid and c.isdefault='Y')order by Cst.CustomerType,Cst.CustomerName ";
                dtCustomer = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "CustomerID";
                gvBrowser.DataSource = (DataTable)dtCustomer;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
    
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if(dtCustomer!=null) dtCustomer.Dispose();
            }
        }
    
        protected void LoadAccessGroupData()
        {
            DataTable dtblAccGroup = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "AccessGroupID", "AccessGroupID", 1, true, false);
                CreateDataColumn(gvBrowser, "Access Group Name", "AccessGroupName", 800, true, true);
    
                string strSQL = "SELECT AccessGroupID,AccessGroupName FROM AccessGroup ";
                dtblAccGroup = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "AccessGroupID";
                gvBrowser.DataSource = (DataTable)dtblAccGroup;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = false;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblAccGroup != null) dtblAccGroup.Dispose();
            }
        }
    
        protected void LoadAccessRightData()
        {
            DataTable dtblAccRight = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "AccessGroupID", "AccessGroupID", 1, true, false);
                CreateDataColumn(gvBrowser, "Access Group", "AccessGroupName", 800, true, true);
    
    
                string strSQL = "SELECT DISTINCT AR.AccessGroupID,AG.AccessGroupName FROM AccessRights AR ";
                strSQL += "INNER JOIN AccessGroup AG ON AR.AccessGroupID=AG.AccessGroupID";
    
    
    
                dtblAccRight = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "AccessGroupID";
                gvBrowser.DataSource = (DataTable)dtblAccRight;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = false;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblAccRight != null) dtblAccRight.Dispose();
            }
        }
    
        protected void LoadLoginUserData()
        {
            DataTable dtblLoginUser = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = false;
    
                CreateDataColumn(gvBrowser, "UserID", "UserID", 1, true, false);
                CreateDataColumn(gvBrowser, "Access Group", "AccessGroupName", 300, true, true);
                CreateDataColumn(gvBrowser, "Login Name", "LoginName", 400, true, true);
                CreateDataColumn(gvBrowser, "User Name", "UserName", 80, true, true);
                CreateDataColumn(gvBrowser, "Email", "Email", 170, true, true);
                CreateDataColumn(gvBrowser, "Privilege", "UserType", 50, true, true);
    
                string strSQL = "SELECT UserID,UserName,LoginName,Email,CASE UserType ";
                strSQL += "WHEN 'L' THEN 'Locked' ";
                strSQL += "WHEN 'S' THEN 'System' ";
                strSQL += "WHEN 'A' THEN 'Admin' ";
                strSQL += "WHEN 'O' THEN 'Other' ";
                strSQL += "End UserType,AG.AccessGroupID,AG.AccessGroupName ";
                strSQL += "FROM [User] U ";
                strSQL += "INNER JOIN ACCESSGROUP AG ON U.AccessGroupID=AG.AccessGroupID Order By UserID desc";
    
                dtblLoginUser = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "UserID";
                gvBrowser.DataSource = (DataTable)dtblLoginUser;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblLoginUser != null) dtblLoginUser.Dispose();
            }
        }
    
        protected void LoadProductData()
        {
            DataTable dtblProductUser = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
                
                CreateDataColumn(gvBrowser, "ProductID", "ProductID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code/Part No.", "ProductCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Description", "ProductName", 400, true, true);
                CreateDataColumn(gvBrowser, "Group", "ProductGroupName", 80, true, true);
                CreateDataColumn(gvBrowser, "Type", "ProductType", 100, true, true);
                CreateDataColumn(gvBrowser, "Selling Unit", "SaleUnitName", 100, true, true);
    
                //string strSQL = "Select P.*,PG.*,PT.*,SU.SaleUnitName from  ";
                //strSQL += "Product P inner join ProductType PT on P.ProductTypeID=PT.ProductTypeID ";
                //strSQL += "inner join ProductGroup PG on P.ProductGroupId=PG.ProductGroupId ";
                //strSQL += "inner join SaleUnit SU on P.SaleUnit=SU.SaleUnitID ";
                //strSQL += "order by P.ProductCode";
    
    
                //amit
                string strSQL = "Select P.ProductID, p.ProductCode, replace(p.Productname, '&#176', '°') ProductName, p.ProductGroupID, p.ProductTypeID, p.ExpProdPlanner,";
                strSQL += " p.HandedProduct, p.ProductFoiled, p.SaleUnit, p.Price, p.DiscountPcnt, p.Premium, p.Surcharge, p.RetailCharge, p.ApplySurcharge,";
                strSQL += " p.ApplyDiscount, p.Height, p.Width, p.Depth, p.LH, p.RH, p.Note,p.OrderComments, p.AddedBy, p.AddTime, p.EditBy, p.EditTime,";
                strSQL += " p.PXML, p.PstyleSheet, p.ProductDirectoryID, p.ProductApplyStyle, p.ProductStyleID, p.ProductPlannerFormula,";
                strSQL += " p.ChildProductPlannerFormula, p.Category,PG.*,PT.*,SU.SaleUnitName from  ";
                strSQL += "Product P inner join ProductType PT on P.ProductTypeID=PT.ProductTypeID ";
                strSQL += "inner join ProductGroup PG on P.ProductGroupId=PG.ProductGroupId ";
                strSQL += "inner join SaleUnit SU on P.SaleUnit=SU.SaleUnitID ";
                strSQL += "order by P.ProductCode";
    
    
    
    
                dtblProductUser = objDbManip.GetDataTable(strSQL);
                
                gvBrowser.KeyFieldName = "ProductID";
                gvBrowser.DataSource = (DataTable)dtblProductUser;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblProductUser != null) dtblProductUser.Dispose();
            }
        }
    
        protected void LoadPersonnelData()
        {
            DataTable dtblPersonnel = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "PersonnelID", "PersonnelID", 1, true, false);
                CreateDataColumn(gvBrowser, "Name", "PersonnelName", 300, true, true);
                CreateDataColumn(gvBrowser, "Commission(%)", "commisionpcnt", 100, true, true);
                CreateDataColumn(gvBrowser, "Type", "PersonnelType", 100, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
                CreateDataColumn(gvBrowser, "Mobile No.", "MobileNo", 100, true, true);
    
                string strSQL = "SELECT P.PersonnelName,  ";
                strSQL += "case P.PersonnelType ";
                strSQL += "when 'DS' then 'Designer' ";
                strSQL += "when 'FT' then 'Fitter' ";
                strSQL += "when 'SP' then 'Sales Person' ";
                strSQL += "else '' end PersonnelType,P.commisionpcnt,P.PersonnelID,P.CityName,P.MobileNo FROM ";
                strSQL += "Personnel P ";//LEFT OUTER JOIN City C on P.CityID=C.CityID ";
                strSQL += "ORDER BY P.PersonnelID desc";
    
                dtblPersonnel = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "PersonnelID";
                gvBrowser.DataSource = (DataTable)dtblPersonnel;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblPersonnel != null) dtblPersonnel.Dispose();
            }
        }
        protected void LoadProcessData()
        {
            DataTable dtblProcess = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ProcessID", "ProcessID", 1, true, false);
                CreateDataColumn(gvBrowser, "Process Name", "ProcessName", 700, true, true);
                CreateDataColumn(gvBrowser, "Process Type", "ProcessTypeName", 100, true, true);
    
                string strSQL = "Select P.ProcessName,PT.ProcessTypeName,P.ProcessID from   ";
                strSQL += "Process P inner join ProcessType PT on P.ProcessTypeId=PT.ProcessTypeId  ";
                strSQL += "order by P.ProcessID desc";
    
                dtblProcess = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "ProcessID";
                gvBrowser.DataSource = (DataTable)dtblProcess;
                gvBrowser.DataBind();
                
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblProcess != null) dtblProcess.Dispose();
            }
        }
        protected void LoadConcessionData()
        {
            DataTable dtblConcession = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ConcessionID", "ConcessionID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code", "ConcessionCode", 150, true, true);
                CreateDataColumn(gvBrowser, "Name", "ConcessionName", 500, true, true);
                CreateDataColumn(gvBrowser, "Commission(%)", "commisionpcnt", 100, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
    
    
                string strSQL = "SELECT CN.ConcessionID,CN.commisionpcnt,CN.ConcessionCode,CN.ConcessionName,CN.CityName FROM Concession CN ";
                strSQL += "ORDER BY CN.ConcessionID desc";
    
                dtblConcession = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "ConcessionID";
                gvBrowser.DataSource = (DataTable)dtblConcession;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblConcession != null) dtblConcession.Dispose();
            }
        }
        protected void LoadFinanceCompanyData()
        {
            DataTable dtblFinance = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "FinanceID", "FinanceID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code", "FinanceCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "FinanceName", 300, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
                CreateDataColumn(gvBrowser, "Post Code", "Postcodename", 100, true, true);
    
                string strSQL = "SELECT F.FinanceCode,F.FinanceID,F.FinanceName,F.CityName,F.Postcodename FROM Finance F ";
                strSQL += "ORDER BY F.FinanceID desc";
    
                dtblFinance = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "FinanceID";
                gvBrowser.DataSource = (DataTable)dtblFinance;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblFinance != null) dtblFinance.Dispose();
            }
        }
    
        protected void LoadMediaData()
        {
            DataTable dtblMEdia = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "MediaID", "MediaID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code", "MediaCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "MediaName", 300, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
    
                string strSQL = "SELECT M.MediaCode,M.MediaName,M.MediaID,M.CityName FROM Media M ";//,ct.MobileNo 
                strSQL += "ORDER BY M.MediaID DESC";
    
                dtblMEdia = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "MediaID";
                gvBrowser.DataSource = (DataTable)dtblMEdia;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblMEdia != null) dtblMEdia.Dispose();
            }
        }
    
        protected void LoadMaterialsData()
        {
            DataTable dtblMaterial = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "MaterialID", "MaterialID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code", "MaterialCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "MaterialName", 250, true, true);
                CreateDataColumn(gvBrowser, "Type", "MaterialTypeName", 80, true, true);
                CreateDataColumn(gvBrowser, "Manufacturer Code", "ManufacturerCode", 200, true, true);
                CreateDataColumn(gvBrowser, "Surcharge", "ApplySurcharge", 50, true, true);
    
                string strSQL = "SELECT M.MaterialCode,M.MaterialName,MT.MaterialTypeName,M.MaterialID,MT.MaterialTypeID,M.ApplySurcharge,MF.ManufacturerCode FROM ";
                strSQL += "Material M INNER JOIN MaterialType MT on M.MaterialTypeID=MT.MaterialTypeID  ";
                strSQL += "INNER JOIN Manufacturer MF on M.ManufacturerID=MF.ManufacturerID  ";
                strSQL += "ORDER BY M.MaterialID DESC";
    
                dtblMaterial = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "MaterialID";
                gvBrowser.DataSource = (DataTable)dtblMaterial;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblMaterial != null) dtblMaterial.Dispose();
            }
        }
        protected void LoadManufacturerData()
        {
            DataTable dtblManufacturer = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ManufacturerID", "ManufacturerID", 1, true, false);
                CreateDataColumn(gvBrowser, "Code", "ManufacturerCode", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "ManufacturerName", 250, true, true);
                CreateDataColumn(gvBrowser, "City", "CityName", 100, true, true);
                CreateDataColumn(gvBrowser, "Mobile No.", "MobileNo", 50, true, true);
    
                string strSQL = "SELECT M.ManufacturerCode,M.ManufacturerID,M.ManufacturerName,M.MobileNo,M.CityName FROM MANUFACTURER M ";
                strSQL += "ORDER BY M.ManufacturerID desc";
    
                dtblManufacturer = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "ManufacturerID";
                gvBrowser.DataSource = (DataTable)dtblManufacturer;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblManufacturer != null) dtblManufacturer.Dispose();
            }
        }
        protected void LoadLetterTemplateData()
        {
            DataTable dtblLetter = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "TemplateLetterID", "TemplateLetterID", 1, true, false);
                CreateDataColumn(gvBrowser, "Letter Template", "LetterType", 800, true, true);
    
                string strSQL = "SELECT TemplateLetterID, ";
                strSQL += "case LetterType  ";
                strSQL += "when 'FP' then 'Final Payment' ";
                strSQL += "when 'OC' then 'Order Confirmation '";
                strSQL += "when 'SR' then 'Second Reminder Letter'";
                strSQL += "else '' end LetterType  FROM TemplateLetter ORDER BY TemplateLetterID desc";
    
                dtblLetter = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "TemplateLetterID";
                gvBrowser.DataSource = (DataTable)dtblLetter;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = false;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblLetter != null) dtblLetter.Dispose();
            }
        }
        protected void LoadEnquiryData()
        {
            DataTable dtblEnquiry = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "EnquiryID", "EnquiryID", 1, true, false);
                CreateDataColumn(gvBrowser, "Enquiry No", "EnquiryNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Date", "EnquiryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Designer", "Designer", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 250, true, true);
                CreateDataColumn(gvBrowser, "CustomerID", "CustomerID", 250, true, false);
                CreateDataColumn(gvBrowser, "PersonnelID", "PersonnelID", 250, true, false);
                CreateDataColumn(gvBrowser, "Requirement", "Requirement", 100, true, true);
                CreateDataColumn(gvBrowser, "Visit Date", "VisitDateTime", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 100, true, true);
    
                string strSQL = "SELECT DISTINCT E.EnquiryNo, ";
                strSQL += "Convert(varchar(10),EnquiryDate,103) as EnquiryDate, ";
                strSQL += "P.PersonnelName Designer,P.PersonnelID ,";
                strSQL += "C.CustomerName,C.CustomerID,";
                strSQL += "CT.MobileNo Phone, ";
                strSQL += "E.Requirement, ";
                strSQL += "Convert(varchar(10),VisitDateTime,103) as VisitDateTime,ST.Status,E.EnquiryID FROM Enquiry E ";
                strSQL += "INNER JOIN CUSTOMER C ON E.CUSTOMERID=C.CUSTOMERID ";
                strSQL += "left outer JOIN PERSONNEL P ON E.DESIGNERID=P.PersonnelID ";
                strSQL += "INNER JOIN STATUS ST ON E.STATUSID=ST.STATUSID ";
                strSQL += "LEFT JOIN CONTACT CT ON CT.ENTITYID=C.CUSTOMERID ";
                strSQL += "AND CT.ENTITYTYPE='CS' ";
                strSQL += "ORDER BY E.EnquiryID DESC ";
    
                dtblEnquiry = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "EnquiryID";
                gvBrowser.DataSource = (DataTable)dtblEnquiry;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblEnquiry != null) dtblEnquiry.Dispose();
            }
        }
        protected void LoadSalesQuotationData()
        {
            DataTable dtblSalesQuotation = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Quotation No.", "SalesQuotationNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Quotation Date", "QuotationDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Quotation Type", "OrderType1", 50, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "CustomerName", 250, true, true);
                CreateDataColumn(gvBrowser, "Mobile No.", "Phone", 100, true, true);
                CreateDataColumn(gvBrowser, "Reference", "QuotationReference", 100, true, true);
                CreateDataColumn(gvBrowser, "Value", "OrderValue", 100, true, true);
                CreateDataColumn(gvBrowser, "VAT", "VatAmt", 100, true, true);
                CreateDataColumn(gvBrowser, "Grand Total", "SaleValue", 100, true, true);
    
                string strSQL = "Select O.OrderID,O.SalesQuotationNo,case When O.OrderType='T' Then 'Trade' Else 'Retail' end OrderType1,O.OrderNo,Convert(varchar(10),O.QuotationDate,103) as QuotationDate,O.QuotationReference, ";
                strSQL += "Convert(varchar(10),O.DeliveryDate,103) as DeliveryDate,O.OrderValue,O.VatAmt,O.SaleValue, ";
                strSQL += "C.CustomerName,C.ResPhoneNo as Phone ";
                strSQL += "from [ORDER] O INNER JOIN Customer C on O.CustomerID=C.CustomerID ";
                strSQL += "where SalesQuotationNo is not null and SalesQuotationNo<>'' and O.IsJob=1 order by O.OrderID desc";
    
                dtblSalesQuotation = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "OrderID";
                gvBrowser.DataSource = (DataTable)dtblSalesQuotation;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblSalesQuotation != null) dtblSalesQuotation.Dispose();
            }
        }
    
        private void DeleteDuplicateRecord()
        {
            try
            {
                objDbManip.ExecNonQuery("Delete from [Order] where ordertype is null " +
                    "or ordervalue=0");
            }
            finally
            {
            }
        }
        protected void LoadGenerateTradeOrderData()
        {
            DataTable dtblGenerateOrder = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 50, true, true);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 200, true, true);
    
                CreateDataColumn(gvBrowser, "Phone", "Phone", 100, true, true);
                CreateDataColumn(gvBrowser, "Quotation Reference", "QuotationReference", 100, true, true);
                CreateDataColumn(gvBrowser, "Value", "OrderValue", 100, true, true);
                CreateDataColumn(gvBrowser, "VAT", "VatAmt", 100, true, true);
                CreateDataColumn(gvBrowser, "Grand Total", "SaleValue", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 50, true, true);
    
                string strSQL = "Select O.OrderID,O.Status,O.OrderNo, Convert(varchar(10),O.OrderDate,103) as OrderDate,O.QuotationReference, ";
                strSQL += "Convert(varchar(10),O.DeliveryDate,103) as DeliveryDate,O.OrderValue,O.VatAmt,O.SaleValue, ";
                strSQL += "C.CustomerName,C.ResPhoneNo as Phone ";
                strSQL += "FROM [ORDER] O inner JOIN Customer C on O.CustomerID=C.CustomerID ";
                strSQL += "where OrderNo is not null and OrderNo<>'' and O.OrderType='T' and O.IsJob=1 ORDER BY O.OrderID Desc";
    
                dtblGenerateOrder = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "OrderID";
                gvBrowser.DataSource = (DataTable)dtblGenerateOrder;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblGenerateOrder != null) dtblGenerateOrder.Dispose();
            }
        }
    
        protected void LoadGenerateRetailOrderData()
        {
            DataTable dtblGenerateOrder = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 250, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Enquiry No.", "EnquiryNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Invoice No.", "InvoiceNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Invoice Date", "InvoiceDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Value", "OrderValue", 100, true, true);
                CreateDataColumn(gvBrowser, "VAT", "VatAmt", 100, true, true);
                CreateDataColumn(gvBrowser, "Sale Value", "SaleValue", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer Value", "CustomerValue", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 50, true, true);
                //sandip
                string strSQL = "GetRetailOrder";
                dtblGenerateOrder = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "OrderID";
                gvBrowser.DataSource = (DataTable)dtblGenerateOrder;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblGenerateOrder != null) dtblGenerateOrder.Dispose();
            }
        }
        protected void LoadGenerateJobData()
        {
            DataTable dtblGenerateJob = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "JobOrderID", "JobOrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Job No.", "JobNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Job Date", "JobDate", 50, true, true);
                CreateDataColumn(gvBrowser, "Quotation No.", "SalesQuotationNo", 100, true, true);
    
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Delivery Date", "DeliveryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Name", "CustomerName", 100, true, true);
                CreateDataColumn(gvBrowser, "Range", "RangeName", 100, true, true);
                CreateDataColumn(gvBrowser, "Delivery Type", "DeliveryType", 50, true, true);
                CreateDataColumn(gvBrowser, "Job Value", "JobValue", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 50, true, true);
    
    
                string strSQL = @"SELECT J.JobNo,J.JobDate,J.JobOrderID,J.JobValue,J.DeliveryDate,
    		                        O.SalesQuotationNo,O.OrderNo,
    		                        C.CustomerName,
    		                        R.RangeName,
    		                        S.Status,
                                    DT.DeliveryTypeName as DeliveryType
                            FROM JobOrder J INNER JOIN [ORDER] O ON J.OrderID=O.OrderID
                            left outer JOIN [Customer] C ON O.CustomerID=C.CustomerID
                            INNER JOIN [Status] S ON S.StatusID=O.StatusID
                            INNER JOIN [Range] R ON R.RangeID=J.RangeID
                            INNER JOIN [DeliveryType] DT ON DT.DeliveryTypeID=J.DeliveryType
                            UNION
                            SELECT J.JobNo,Convert(varchar(10),J.JobDate,103),J.JobOrderID,J.JobValue,Convert(varchar(10),J.DeliveryDate,103),
    		                        O.SalesQuotationNo,O.OrderNo,
    		                        C.CustomerName,
    		                        R.RangeName,
    		                        S.Status,
                                    DT.DeliveryTypeName as DeliveryType
                            FROM JobOrder J INNER JOIN [ORDER] O ON J.QuotationID=O.OrderID
                            left outer JOIN [Customer] C ON O.CustomerID=C.CustomerID
                            INNER JOIN [Status] S ON S.StatusID=O.StatusID
                            INNER JOIN [Range] R ON R.RangeID=J.RangeID
                            INNER JOIN [DeliveryType] DT ON DT.DeliveryTypeID=J.DeliveryType
                            ORDER BY J.JobOrderID Desc";
    
                dtblGenerateJob = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "JobOrderID";
                gvBrowser.DataSource = (DataTable)dtblGenerateJob;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
    
                if (dtblGenerateJob != null) dtblGenerateJob.Dispose();
            }
        }
    
        protected void LoadProductionAdviceData()
        {
            DataTable dtblProductionAdvice = null;
       
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ProductionAdviceID", "ProductionAdviceID", 1, true, false);
                CreateDataColumn(gvBrowser, "Advice No.", "ProductionAdviceNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Advice Date", "ProductionAdviceDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Job No.", "JobNo", 100, true, true);
    
                CreateDataColumn(gvBrowser, "Customer", "CustomerName", 100, true, true);
                CreateDataColumn(gvBrowser, "Job Reference", "JobReference", 100, true, true);
                CreateDataColumn(gvBrowser, "Production Date", "ProductionDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Complete Date", "CompleteDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 100, true, true);
    
    
                string strSQL = @"Select p.ProductionAdviceID,p.ProductionAdviceNo,
                            Convert(Varchar(10),p.ProductionAdviceDate,103)ProductionAdviceDate,
                            J.JobNo,C.CustomerName,
                            J.JobReference,Convert(Varchar(10),p.ProductionDate,103)ProductionDate,
                            Convert(Varchar(10),p.CompleteDate,103)CompleteDate,S.Status
                            from ProductionAdvice p
                            Inner Join JobOrder J on p.JobOrderID = J.JobOrderID
                            Inner Join [Order] O on O.OrderID = J.OrderID
                            Inner Join Customer C on C.CustomerID = O.CustomerID
                            Inner Join Status S on S.StatusID = p.Status Order By p.ProductionAdviceNo Desc";
    
                dtblProductionAdvice = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "ProductionAdviceID";
                gvBrowser.DataSource = (DataTable)dtblProductionAdvice;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = false;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblProductionAdvice != null) dtblProductionAdvice.Dispose();
            }
        }
    
        protected void LoadProductionCompletionData()
        {
            DataTable dtblProductionCompletion = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ProductionAdviceID", "ProductionAdviceID", 1, true, false);
                CreateDataColumn(gvBrowser, "Advice No.", "ProductionAdviceNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Job No.", "ProductionAdviceDate", 100, true, true);
    
                CreateDataColumn(gvBrowser, "Customer", "CustomerName", 200, true, true);
                CreateDataColumn(gvBrowser, "Job Reference", "JobReference", 100, true, true);
                CreateDataColumn(gvBrowser, "Production Date", "ProductionDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Complete Date", "CompleteDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 100, true, true);
    
    
                string strSQL = @"Select p.ProductionAdviceID,p.ProductionAdviceNo,
                            Convert(Varchar(10),p.ProductionAdviceDate,103)ProductionAdviceDate,
                            J.JobNo,C.CustomerName,
                            J.JobReference,Convert(Varchar(10),p.ProductionDate,103)ProductionDate,
                            Convert(Varchar(10),p.CompleteDate,103)CompleteDate,S.Status
                            from ProductionAdvice p
                            Inner Join JobOrder J on p.JobOrderID = J.JobOrderID
                            Inner Join [Order] O on O.OrderID = J.OrderID
                            Inner Join Customer C on C.CustomerID = O.CustomerID
                            Inner Join Status S on S.StatusID = p.Status";
    
                dtblProductionCompletion = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "ProductionAdviceID";
                gvBrowser.DataSource = (DataTable)dtblProductionCompletion;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblProductionCompletion != null) dtblProductionCompletion.Dispose();
            }
        }
            
        protected void LoadGenerateTradeInvoiceData()
        {
            DataTable dtblGenTradeInvoice = null;
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "InvoiceHdrID", "InvoiceHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Invoice No.", "InvoiceNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Invoice Date", "InvoiceDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer", "CustomerName", 200, true, true);
                //CreateDataColumn(gvBrowser, "Amount", "Amount", 100, true, true);
    
    
                string strSQL = "Select IH.InvoiceNo,Convert(Varchar(10),IH.InvoiceDate,103) InvoiceDate,O.OrderNo,C.CustomerName,IH.TotalValue as Amount,IH.InvoiceHdrID " +
                            " From InvoiceHdr IH" +
                            " Inner Join [Order] O on O.OrderID=IH.OrderID AND O.InvoiceNo=IH.InvoiceNo" +
                            " Inner Join Customer C on C.CustomerID=O.CustomerID" +
                            " Where IH.InvoiceType='T' Order by IH.InvoiceHdrID desc";
    
                dtblGenTradeInvoice = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "InvoiceHdrID";
                gvBrowser.DataSource = (DataTable)dtblGenTradeInvoice;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblGenTradeInvoice != null) dtblGenTradeInvoice.Dispose();
            }
        }
    
        protected void LoadGenerateRetailInvoiceData()
        {
            DataTable dtblGenRetailInvoice = null;
           
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "InvoiceHdrID", "InvoiceHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Invoice No.", "InvoiceNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Invoice Date", "InvoiceDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer", "CustomerName", 200, true, true);
                //CreateDataColumn(gvBrowser, "Amount", "Amount", 100, true, true);
    
    
                string strSQL = "Select IH.InvoiceNo,Convert(Varchar(10),IH.InvoiceDate,103) InvoiceDate,O.OrderNo,C.CustomerName,IH.TotalValue as Amount,IH.InvoiceHdrID " +
                                " From InvoiceHdr IH" +
                                " Inner Join [Order] O on O.OrderID=IH.OrderID" +
                                " Inner Join Customer C on C.CustomerID=O.CustomerID" +
                                " Where IH.InvoiceType='R' Order by IH.InvoiceHdrID desc";
    
                dtblGenRetailInvoice = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "InvoiceHdrID";
                gvBrowser.DataSource = (DataTable)dtblGenRetailInvoice;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblGenRetailInvoice != null) dtblGenRetailInvoice.Dispose();
            }
        }
        protected void LoadDespatchJob()
        {
            DataTable dtblDespatchJob = null;
           
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "JobOrderID", "JobOrderID", 1, true, false);
                //CreateDataColumn(gvBrowser, "Delivery Note", "DeliveryNote", 100, true, true);
                //CreateDataColumn(gvBrowser, "Date", "DeliveryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Invoice No.", "InvoiceNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Invoice Date", "InvoiceDate", 200, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Job No.", "JobNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 100, true, true);
                CreateDataColumn(gvBrowser, "Despatch Date", "DespatchDate", 100, true, true);
    
                //CreateDataColumn(gvBrowser, "Range", "RangeName", 100, true, true);
                //CreateDataColumn(gvBrowser, "Delivery W/C", "DeliveryWC", 100, true, true);
    
                /*string strSQL = string.Format(@"SELECT DNH.DeliveryNote,DNH.DeliveryNoteHdrID,IH.InvoiceNo,CONVERT(VARCHAR(10),JO.DeliveryDate,103) DeliveryDate,
                                        CONVERT(VARCHAR(10),O.OrderDate,103) OrderDate,R.RangeName,Isnull(WC.WarrantyCertificateNo,'') DeliveryWC
                                        FROM DeliveryNoteHdr DNH
                                        INNER JOIN InvoiceHdr IH ON DNH.InvoiceHdrID=IH.InvoiceHdrID
                                        left JOIN JobOrder JO ON DNH.JobOrderID=JO.JobOrderID
                                        LEFT OUTER JOIN [ORDER] O ON JO.OrderID=O.OrderID
                                        LEFT OUTER JOIN [Range] R ON JO.RangeID=R.RangeID 
    									LEFT OUTER JOIN WarrantyCertificate WC on WC.OrderID=O.OrderID ORDER BY DNH.DeliveryNoteHdrID desc");*/
                string strSQL = string.Format(@"select C.CustomerName,j.JobOrderID,o.InvoiceNo,CONVERT(VARCHAR(10),o.InvoiceDate,103) InvoiceDate,o.OrderNo,CONVERT(VARCHAR(10),o.OrderDate,103) OrderDate,(Convert(varchar(10),J.OrderID)+'_'+J.JobNo) as JobNo,j.DespatchDate from JobOrder j,
                                               [Order] o,Customer C where o.CustomerID=C.CustomerID and o.OrderID=j.OrderID and j.DespatchDate is not Null
                                               and o.IsJob=1 Order By j.DespatchDate Desc");
    
                dtblDespatchJob = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "JobOrderID";
                gvBrowser.DataSource = (DataTable)dtblDespatchJob;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblDespatchJob != null) dtblDespatchJob.Dispose();
            }
        }
    
        protected void LoadDeliveryJob()
        {
            DataTable dtblDeliveryJob = null;
    
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "JobOrderID", "JobOrderID", 1, true, false);
                //CreateDataColumn(gvBrowser, "Delivery Note", "DeliveryNote", 100, true, true);
                //CreateDataColumn(gvBrowser, "Invoice No.", "InvoiceNo", 200, true, true);
                //CreateDataColumn(gvBrowser, "Invoice Date", "InvoiceDate", 200, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Order Type", "OrderType", 200, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Job No.", "JobNo", 100, true, true);
                CreateDataColumn(gvBrowser, "CustomerName", "CustomerName", 100, true, true);
                CreateDataColumn(gvBrowser, "Delivery Date", "DeliveryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Delivery W/C", "StartDate", 100, true, true);
    
                string strSQL = string.Format(@"select C.CustomerName,j.JobOrderID,case When o.OrderType='T' Then 'Trade' Else 'Retail' end OrderType,CONVERT(VARCHAR(10),j.StartDate,103)as StartDate,o.OrderNo,CONVERT(VARCHAR(10),o.OrderDate,103) OrderDate,(Convert(varchar(10),J.OrderID)+'_'+J.JobNo) as JobNo,CONVERT(VARCHAR(10),j.DeliveryDate,103) as DeliveryDate from JobOrder j,
                                               [Order] o,[Customer] C where o.CustomerID=C.CustomerID and o.OrderID=j.OrderID and j.DeliveryDate is not Null
                                               and o.IsJob=1 Order By j.DeliveryDate Desc");
    
                dtblDeliveryJob = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "JobOrderID";
                gvBrowser.DataSource = (DataTable)dtblDeliveryJob;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblDeliveryJob != null) dtblDeliveryJob.Dispose();
            }
        }
    
        protected void LoadUpdateFitterSheetData()
        {
            DataTable dtblUpdateFitterSheet = null;
       
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = false;
    
                CreateDataColumn(gvBrowser, "FitSheetHdrID", "FitSheetHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Date", "DeliveryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Fitter", "Fitter", 200, true, true);
                CreateDataColumn(gvBrowser, "Name", "CustomerName", 200, true, true);
                CreateDataColumn(gvBrowser, "Cash/Finance", "CashFinance", 100, true, true);
                CreateDataColumn(gvBrowser, "Compeleted", "Compeleted", 100, true, true);
    
                string strSQL = string.Format(@"SELECT FSH.FitSheetHdrID,O.OrderNo,
                                Convert(Varchar(10),O.DeliveryDate,103) DeliveryDate,
                                P.PersonnelName Fitter,C.CustomerName,'' CashFinance,
                                Case 
                                When Complete='Y' Then 'Yes'
                                When Complete='N' Then 'No'
                                End as Compeleted
                                FROM FitSheetHdr FSH
                                INNER JOIN DeliveryNoteHdr DN ON DN.DeliveryNoteHdrID=FSH.DeliveryNoteHdrID
                                INNER JOIN JobOrder JB ON JB.JobOrderID=DN.JobOrderID
                                INNER JOIN [Order] O ON O.OrderID=JB.OrderID
                                INNER JOIN Personnel P ON O.FitterID=P.PersonnelID
                                INNER JOIN Customer C ON C.CustomerID=O.CustomerID
                                AND P.PERSONNELTYPE='FT' Order by FSH.FitSheetHdrID");
                //INNER JOIN ScheduleHdr SH ON FSH.ScheduleHDrID=SH.ScheduleHDrID
    
                dtblUpdateFitterSheet = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "FitSheetHdrID";
                gvBrowser.DataSource = (DataTable)dtblUpdateFitterSheet;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblUpdateFitterSheet != null) dtblUpdateFitterSheet.Dispose();
            }
        }
    
        protected void LoadWarrantyCertificateData()
        {
            DataTable dtblWarrantyCertificate = null;
        
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = false;
    
                CreateDataColumn(gvBrowser, "WarrantyCertificateID", "WarrantyCertificateID", 1, true, false);
                CreateDataColumn(gvBrowser, "Warranty Certificate No.", "WarrantyCertificateNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Start Date", "StartDate", 100, true, true);
                CreateDataColumn(gvBrowser, "End Date", "EndDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Concession Name", "ConcessionName", 200, true, true);
    
                string strSQL = string.Format(@"SELECT WarrantyCertificateID,WarrantyCertificateNo,CONVERT(VARCHAR(10),StartDate,103) StartDate,CONVERT(VARCHAR(10),EndDate,103) EndDate,O.OrderNo,C.ConcessionName
                                            FROM WarrantyCertificate WC
                                            INNER JOIN [ORDER] O ON WC.OrderID=O.OrderID
                                            LEFT JOIN CONCESSION C ON WC.CONCESSIONID=C.CONCESSIONID ORDER BY WarrantyCertificateID DESC");
    
                dtblWarrantyCertificate = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "WarrantyCertificateID";
                gvBrowser.DataSource = (DataTable)dtblWarrantyCertificate;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblWarrantyCertificate != null) dtblWarrantyCertificate.Dispose();
            }
        }
        protected void LoadOrderClosureData()
        {
            DataTable dtblOrderClosure = null;
       
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = false;
    
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 250, true, true);
                CreateDataColumn(gvBrowser, "Phone", "MobileNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Requirement", "Requirement", 100, true, true);
    
                CreateDataColumn(gvBrowser, "Require Date", "RequireDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Close", "OrderClosure", 100, true, true);
                CreateDataColumn(gvBrowser, "Survey Done", "SurveyDone", 100, true, true);
    
                string strSQL = string.Format(@"SELECT OrderNo,
                                            CONVERT(VARCHAR(10),OrderDate,103) OrderDate,
                                            CustomerName,
                                            MobileNo,
                                            Requirement,
                                            '' RequireDate,
                                            OrderClosure,
                                            SurveyDone,
                                            OrderID FROM [ORDER] O
                                            INNER JOIN CUSTOMER C ON O.CustomerID=C.CustomerID 
                                            ORDER BY OrderID DESC");
    
                dtblOrderClosure = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "OrderID";
                gvBrowser.DataSource = (DataTable)dtblOrderClosure;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblOrderClosure != null) dtblOrderClosure.Dispose();
            }
        }
    
        protected void LoadAdvertisementDtlData()
        {
            DataTable dtblAdvertisementDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ADSHdrID", "ADSHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Date", "DocDate", 150, true, true);
                CreateDataColumn(gvBrowser, "Media Name", "MediaName", 500, true, true);
                CreateDataColumn(gvBrowser, "Document No.", "DocNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Paid", "Pay", 100, true, true);
                CreateDataColumn(gvBrowser, "Amount", "TotalAmount", 250, true, true);
    
                string strSQL = "SELECT Media.MediaName,ADSHdr.Pay,ADSHdr.ADSHdrID,ADSHdr.DocNo,Convert(Varchar(10),ADSHdr.DocDate,103) as DocDate,Convert(Decimal(10,2),ADSHdr.TotalAmount) TotalAmount FROM ADSHdr Inner join Media on Media.MediaID=ADSHdr.MediaID Order By ADSHdrID Desc";
    
                dtblAdvertisementDtl = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "ADSHdrID";
                gvBrowser.DataSource = (DataTable)dtblAdvertisementDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblAdvertisementDtl != null) dtblAdvertisementDtl.Dispose();
            }
        }
    
        protected void LoadMediaDtlData()
        {
            DeleteGridViewColumns();
            gvBrowser.DataSource = null;
        }
    
        protected void LoadPaymentConfirmationDtlData()
        {
            DataTable dtblPaymentDtl = null;
            
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = false;
    
                CreateDataColumn(gvBrowser, "ADSHdrID", "ADSHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Document No", "DocNo", 150, true, true);
                CreateDataColumn(gvBrowser, "Date", "DocDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Media Name", "MediaName", 500, true, true);
                CreateDataColumn(gvBrowser, "Amount", "Amount", 100, true, true);
                CreateDataColumn(gvBrowser, "Confirm", "PaymentConfirmation", 50, true, true);
    
                string strSQL = @"Select H.ADSHdrID,isnull(A.ADSDtlID,0) ADSDtlID,H.DocNo,
                            Convert(Varchar(10),H.DocDate,103) DocDate,M.MediaName,isnull(A.Amount,0) Amount,
                            Case When A.PaymentConfirmation = 'Y' Then 'Yes' Else 'No'End PaymentConfirmation 
                            From Media M
                            Left Join ADSDtl A on M.MediaID = A.MediaID
                            INNER JOIN ADSHdr H on A.ADSHdrID = H.ADSHdrID Order by H.ADSHdrID desc";
    
    
                dtblPaymentDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "ADSHdrID";
                gvBrowser.DataSource = (DataTable)dtblPaymentDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblPaymentDtl != null) dtblPaymentDtl.Dispose();
            }
        }
    
        protected void LoadCalculateCommissionWagesDtlData()
        {
                        DataTable dtblCommissionWagesDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "PersonnelId", "PersonnelId", 1, true, false);
                CreateDataColumn(gvBrowser, "Entity Name", "EntityName", 300, true, true);
                CreateDataColumn(gvBrowser, "Entity Type", "EntityType", 150, true, true);
                CreateDataColumn(gvBrowser, "Document No", "DocumentNo", 250, true, true);
                CreateDataColumn(gvBrowser, "Amount", "Amount", 50, true, true);
    
                string strSQL = "Select '' Code,'' Name,'' City, '' " +
                            "Country,''Mobile,0 PersonnelId " +
                                "From [AccessGroup] where AccessGroupId=-1";
    
    
                dtblCommissionWagesDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "PersonnelId";
                gvBrowser.DataSource = (DataTable)dtblCommissionWagesDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblCommissionWagesDtl != null) dtblCommissionWagesDtl.Dispose();
            }
        }
    
        protected void LoadManageAbsentDtlData()
        {
            DataTable dtblManageAbsentDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ManageAbsentHdrID", "ManageAbsentHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Year", "AbsentYear", 150, true, true);
                CreateDataColumn(gvBrowser, "Month", "AbsentMonthName", 700, true, true);
                string strSQL = "Select ManageAbsentHdrID,Convert(Varchar(5),AbsentYear) AbsentYear,AbsentMonth,DateName(m,Convert(datetime,'01/' + Convert(varchar,AbsentMonth) + '/' +Convert(varchar,AbsentYear) ,103)) as AbsentMonthName from ManageAbsentHdr order by AbsentYear desc,AbsentMonth desc";
    
                dtblManageAbsentDtl = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "ManageAbsentHdrID";
                gvBrowser.DataSource = (DataTable)dtblManageAbsentDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblManageAbsentDtl != null) dtblManageAbsentDtl.Dispose();
            }
        }
    
        protected void LoadPaymentDtlData()
        {
            DataTable dtblPaymentDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "PaymentHdrID", "PaymentHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 300, true, true);
                CreateDataColumn(gvBrowser, "Order No", "OrderNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Order Type", "OrderType", 120, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 150, true, true);
                CreateDataColumn(gvBrowser, "Order Value", "OrderValue", 200, true, true);
                CreateDataColumn(gvBrowser, "Outstanding Amount", "OutstandingAmt", 200, true, true);
                CreateDataColumn(gvBrowser, "Status", "Status", 100, true, true);
    
                string strSQL = "Select P.OrderID,P.PaymentHdrID,case When OD.OrderType='T' Then 'Trade' Else 'Retail' end OrderType,C.CustomerName,OD.OrderNo,Convert(varchar(10),P.OrderDate,103) as OrderDate,IsNull(P.OrderValue,0) OrderValue,IsNull(P.OutstandingAmt,0) OutstandingAmt,case When IsNull(P.OutstandingAmt,0)>0 Then 'Due' When IsNull(P.OrderValue,0)=0 Then 'Due' Else 'Paid' end Status From PaymentHdr P  LEFT OUTER JOIN [ORDER] OD ON P.OrderID=OD.OrderID Inner Join Customer C On C.CustomerID=P.CustomerID Order by P.PaymentHdrID Desc";//where PaymentHdrID in ( Select PaymentHdrID from PaymentDtl where EntityType <> 'MD') Order by PaymentHdrID desc";
    
                dtblPaymentDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "PaymentHdrID";
                gvBrowser.DataSource = (DataTable)dtblPaymentDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblPaymentDtl != null) dtblPaymentDtl.Dispose();
            }
        }
    
        protected void LoadRetailSalesData()
        {
            DataTable dtblPaymentDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "Payment", "PaymentHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 300, true, true);
                CreateDataColumn(gvBrowser, "Enquiry No", "EnquiryNo", 200, true, true);
                CreateDataColumn(gvBrowser, "Date", "EnquiryDate", 150, true, true);
                CreateDataColumn(gvBrowser, "Order Value", "OrderValue", 200, true, true);
                CreateDataColumn(gvBrowser, "Outstanding Amount", "OutstandingAmt", 200, true, true);
    
                string strSQL = "select paymenthdr.*,paymentDtl.Amount,Convert(varchar(10),EnquiryDate,103) as EnquiryDate,Customer.CustomerName  from paymenthdr Inner Join paymentDtl on paymentDtl.PaymentHdrID=paymenthdr.PaymentHdrID Inner Join Enquiry on paymenthdr.EnquiryNo=Enquiry.EnquiryNo Inner Join Customer on Customer.CustomerID=paymenthdr.CustomerID Where paymenthdr.orderID Is Null And PaymentDtl.PaymentType=1 Order By EnquiryNo desc ";//,OD.OrderNo,Convert(Varchar(10),P.OrderDate,103) OrderDate
    
                dtblPaymentDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "PaymentHdrID";
                gvBrowser.DataSource = (DataTable)dtblPaymentDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblPaymentDtl != null) dtblPaymentDtl.Dispose();
            }
        }
    
        protected void LoadReceiptDtlData()
        {
            DataTable dtblReceiptDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "ReceiptHdrID", "ReceiptHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Document No", "DocNo", 500, true, true);
                CreateDataColumn(gvBrowser, "Date", "DocDate", 150, true, true);
                CreateDataColumn(gvBrowser, "Total Amount", "TotalAmount", 200, true, true);
    
                string strSQL = "Select ReceiptHdrID,DocNo,Convert(varchar(10),DocDate,103)as DocDate,IsNull(TotalAmount,0) TotalAmount From ReceiptHdr  Order by ReceiptHdrID Desc";
    
                dtblReceiptDtl = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "ReceiptHdrID";
                gvBrowser.DataSource = (DataTable)dtblReceiptDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblReceiptDtl != null) dtblReceiptDtl.Dispose();
            }
        }
    
        protected void LoadConcessionPaymentDtlData()
        {
            DataTable dtblReceiptDtl = null;
    
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "CommissionConcessionID", "CommissionConcessionID", 1, true, false);
                CreateDataColumn(gvBrowser, "ConcessionID", "ConcessionID", 1, true, false);
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Commission", "CommissionAmount", 100, true, true);
                CreateDataColumn(gvBrowser, "Commission Paid", "CommissionPaid", 100, true, true);
                CreateDataColumn(gvBrowser, "Commission Outstanding", "OutstandingCommAmt", 100, true, true);
                CreateDataColumn(gvBrowser, "Lead", "LeadCommission", 100, true, true);
                CreateDataColumn(gvBrowser, "Lead Paid", "LeadCommissionPaid", 100, true, true);
                CreateDataColumn(gvBrowser, "Lead Outstanding", "OutstandingLeadAmt", 100, true, true);
    
                string strSQL = "Select CommissionAmount,CommissionConcessionID,ConcessionID,OrderID,OrderNo,Convert(varchar(10),OrderDate,103) as OrderDate,CommissionPaid,LeadCommission,LeadCommissionPaid,case when commissionPayable=0 then 0 when commissionPayable=1 then (CommissionAmount-CommissionPaid) end OutstandingCommAmt,(LeadCommission-LeadCommissionPaid)As OutstandingLeadAmt, case when commissionPayable=0 then 0 when commissionPayable=1 then CommissionAmount end CommissionAmount from CommissionConcession order by OrderID desc";
    
                dtblReceiptDtl = objDbManip.GetDataTable(strSQL);
                gvBrowser.KeyFieldName = "CommissionConcessionID";
                gvBrowser.DataSource = (DataTable)dtblReceiptDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblReceiptDtl != null) dtblReceiptDtl.Dispose();
            }
        }
    
    
        protected void LoadDesignerPaymentDtlData()
        {
            DataTable dtblReceiptDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "PaymentHdrID", "PaymentHdrID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer", "CustomerName", 200, true, true);
                CreateDataColumn(gvBrowser, "Designer", "Designer", 200, true, true);
                CreateDataColumn(gvBrowser, "Enquiry No.", "EnquiryNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Enquiry Date", "EnquiryDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Commission", "DesignerCommission", 100, true, true);
                CreateDataColumn(gvBrowser, "Commission Paid", "DesignerCommissionPaid", 100, true, true);
                CreateDataColumn(gvBrowser, "Outstanding Amount", "OutstandingAmt", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Value", "OrderValue", 100, true, true);
    
                string strSQL = "Select P.PaymentHdrID,P.EnquiryNo,Convert(Varchar(10),E.EnquiryDate,103) as EnquiryDate, ";
                strSQL += "PR.PersonnelName as Designer, ";
                strSQL += "P.DesignerCommissionPaid,(P.DesignerCommission-P.DesignerCommissionPaid)As OutstandingAmt, ";
                strSQL += "DesignerCommission=case when P.DesignerExCommDue=1 Then (P.DesignerCommission + P.DesignerCommExtraAmt)else P.DesignerCommission end,";
                strSQL += "C.CustomerName,C.ResPhoneNo as Phone,";
                strSQL += "P.OrderValue ";
                strSQL += "from [PaymentHdr] P ";
                strSQL += "left outer JOIN Customer C on P.CustomerID=C.CustomerID ";
                strSQL += "Inner Join [Personnel] PR ON PR.PersonnelID=P.DesignerID and PR.PersonnelType='DS' ";
                strSQL += "left outer join Enquiry E on E.EnquiryNo=P.EnquiryNo ";
                strSQL += "where P.EnquiryNo is not null and P.EnquiryNo<>0 ORDER BY P.PaymentHdrID Desc ";
    
                dtblReceiptDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "PaymentHdrID";
                gvBrowser.DataSource = (DataTable)dtblReceiptDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblReceiptDtl != null) dtblReceiptDtl.Dispose();
            }
        }
    
        protected void LoadFitterPaymentDtlData()
        {
            DataTable dtblReceiptDtl = null;
    
            try
            {
                DeleteGridViewColumns();
    
                gvBrowser.DataSource = null;
                gvBrowser.Columns[0].Visible = true;
    
                CreateDataColumn(gvBrowser, "OrderID", "OrderID", 1, true, false);
                CreateDataColumn(gvBrowser, "Customer Name", "CustomerName", 200, true, true);
                CreateDataColumn(gvBrowser, "Fitter Name", "PersonnelName", 500, true, true);
                CreateDataColumn(gvBrowser, "Order No.", "OrderNo", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Date", "OrderDate", 100, true, true);
                CreateDataColumn(gvBrowser, "Order Value", "OrderValue", 200, true, true);
                CreateDataColumn(gvBrowser, "Fitter Commission", "FitterCommission", 100, true, true);
                CreateDataColumn(gvBrowser, "Fitter Commission Paid", "FitterCommissionPaid", 100, true, true);
                CreateDataColumn(gvBrowser, "Outstanding Amount", "OutstandingAmt", 100, true, true);
    
    
                string strSQL = " Select O.OrderID,O.Status,O.OrderNo,O.FitterCommission,";
                strSQL += "O.FitterCommissionPaid,P.PersonnelName,(O.FitterCommission-O.FitterCommissionPaid)As OutstandingAmt,";
                strSQL += "Convert(varchar(10),O.OrderDate,103) as OrderDate,O.QuotationReference,";
                strSQL += "Convert(varchar(10),O.DeliveryDate,103) as DeliveryDate,O.OrderValue,O.VatAmt,O.SaleValue, ";
                strSQL += "C.CustomerName,C.ResPhoneNo as Phone ,R.RangeName from [ORDER] O ";
                strSQL += "left outer JOIN Customer C on O.CustomerID=C.CustomerID ";
                strSQL += "Left outer JOIN [Range] R ON R.RangeID=O.RangeID ";
                strSQL += "Inner Join [Personnel] P ON P.PersonnelID=O.FitterID and P.PersonnelType='FT'";
                strSQL += "where OrderNo is not null and OrderNo<>''ORDER BY O.OrderID Desc";
    
                dtblReceiptDtl = objDbManip.GetDataTable(strSQL);
    
                gvBrowser.KeyFieldName = "OrderID";
                gvBrowser.DataSource = (DataTable)dtblReceiptDtl;
                gvBrowser.DataBind();
    
                gvBrowser.Settings.ShowFilterRow = true;
                gvBrowser.Settings.ShowGroupPanel = true;
                gvBrowser.FocusedRowIndex = 0;
                gvBrowser.SettingsBehavior.AllowFocusedRow = true;
            }
            finally
            {
                if (dtblReceiptDtl != null) dtblReceiptDtl.Dispose();
            }
        }
    
    
        protected void LoadLookUpData()
        {
            DeleteGridViewColumns();
    
            gvBrowser.DataSource = null;
            gvBrowser.Columns[0].Visible = false;
    
            CreateDataColumn(gvBrowser, "ID", "ID", 1, true, false);
            CreateDataColumn(gvBrowser, "Lookup Name", "LookupName", 800, true, true);
    
            gvBrowser.KeyFieldName = "ID";
            gvBrowser.DataSource = GenerateLookUpTable();
            gvBrowser.DataBind();
    
            gvBrowser.Settings.ShowFilterRow = false;
            gvBrowser.Settings.ShowGroupPanel = false;
            gvBrowser.FocusedRowIndex = 0;
            gvBrowser.SettingsBehavior.AllowFocusedRow = true;
    
        }
    
        private DataTable GenerateLookUpTable()
        {
            DataTable dtblLookUP = null;
            try
            {
                dtblLookUP = new DataTable();
                dtblLookUP.Columns.Add("LookupName", System.Type.GetType("System.String"));
                dtblLookUP.Columns.Add("ID", System.Type.GetType("System.Int32"));
    
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Rate",
                                                1
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Range",
                                                2
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Route Style",
                                                3
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Material Type",
                                                4
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Payment Mode",
                                                5
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Product Group",
                                                6
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Product Type",
                                                7
    
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Sale Unit",
                                                8
    
                                                
                                            });
                dtblLookUP.Rows.Add(new object[]
                                            {
                                                "Process Type",
                                                9
    
                                                
                                            });
            }
            catch { }
            return dtblLookUP;
        }
    
        protected void DeleteGridViewColumns()
        {
            while (gvBrowser.Columns.Count > 1)
            {
                if (gvBrowser.Columns[1].Caption != "Delete")
                {
                    gvBrowser.Columns.Remove(gvBrowser.Columns[1]);
                }
            }
        }
    
        private void HideButton(bool boolFlag)
        {
            if (boolFlag)
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                sb.Append("<script language='javascript' type='text/javascript'>");
                sb.Append("alert('hallo');");
                sb.Append("function HideButton()");
                sb.Append("{var x=document.getElementById('btnAdd');");
                sb.Append("x.style.visibility = 'hidden';");
                sb.Append("}");
                sb.Append("</script>");
    
                this.Page.RegisterClientScriptBlock("DoubleClickScript", sb.ToString());
            }
            else
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                sb.Append("<script language='javascript' type='text/javascript'>");
                sb.Append("function UnHideButton()");
                sb.Append("{var x=document.getElementById('btnAdd');");
                sb.Append("x.style.visibility = 'visible';");
                sb.Append("}");
                sb.Append("</script>");
    
                this.Page.RegisterClientScriptBlock("DoubleClickScript", sb.ToString());
            }
        }
    
        protected void btnAdd_Click(object sender, EventArgs e)
        {
    
        }
    
        private void DeleteRowsOfGrid(string tblName, string cntrlID, string str)
        {
                string Delete = "Delete from " + tblName + " where " + cntrlID + " in(" + (str) + ")";
                objDbManip.ExecNonQuery(Delete);
        }
    
        private void DeleteRowsOfEnquiryGrid(string tblName, string cntrlID, string str)
        {
            try
            {
                if (objDbManip.GetDataTable("Select OrderID from [Order] Where EnquiryNo=dbo.PADL('" + str + "',10,'0')").Rows.Count == 0)
                {
                    objDbManip.ExecNonQuery("delete from " + tblName + " where " + cntrlID + " in(" + (str) + ")");
                    objDbManip.ExecNonQuery("delete from paymenthdr where EnquiryNo=dbo.PADL('" + str + "',10,'0')");
                    objDbManip.ExecNonQuery("delete from paymentdtl where paymenthdrid not in (select paymenthdrID from paymenthdr)");
                    objDbManip.ExecNonQuery("delete from schedule where Convert(int,enquiryno)="+str);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Msg", "alert('Please Delete Order First');", true);
                }
            }
            catch {}
            finally
            {
            }
        }
        private void DeleteRowsOfDespatch(string tblName, string cntrlID, string str)
        {
            try
            {
                //if (objDbManip.GetDataTable("Select OrderID from [Order] Where EnquiryNo=dbo.PADL('" + str + "',10,'0')").Rows.Count == 0)
                //{
                //    objDbManip.ExecNonQuery("delete from " + tblName + " where " + cntrlID + " in(" + (str) + ")");
                //    objDbManip.ExecNonQuery("delete from paymenthdr where EnquiryNo=dbo.PADL('" + str + "',10,'0')");
                //    objDbManip.ExecNonQuery("delete from paymentdtl where paymenthdrid not in (select paymenthdrID from paymenthdr)");
                //    objDbManip.ExecNonQuery("delete from schedule where Convert(int,enquiryno)=" + str);
                //}
                //else
                //{
                //    ScriptManager.RegisterStartupScript(this, this.GetType(), "Msg", "alert('Please Delete Order First');", true);
                //}
                objDbManip.ExecNonQuery("Update " + tblName + " Set DespatchDate=Null,StatusID=14  where " + cntrlID + " in(" + (str) + ")");
    
            }
            catch { }
            finally
            {
            }
        }
    
        private void DeleteRowsOfDelivery(string tblName, string cntrlID, string str)
        {
            try
            {
                //if (objDbManip.GetDataTable("Select OrderID from [Order] Where EnquiryNo=dbo.PADL('" + str + "',10,'0')").Rows.Count == 0)
                //{
                //    objDbManip.ExecNonQuery("delete from " + tblName + " where " + cntrlID + " in(" + (str) + ")");
                //    objDbManip.ExecNonQuery("delete from paymenthdr where EnquiryNo=dbo.PADL('" + str + "',10,'0')");
                //    objDbManip.ExecNonQuery("delete from paymentdtl where paymenthdrid not in (select paymenthdrID from paymenthdr)");
                //    objDbManip.ExecNonQuery("delete from schedule where Convert(int,enquiryno)=" + str);
                //}
                //else
                //{
                //    ScriptManager.RegisterStartupScript(this, this.GetType(), "Msg", "alert('Please Delete Order First');", true);
                //}
                objDbManip.ExecNonQuery("Update " + tblName + " Set DeliveryDate=Null,StatusID=14  where " + cntrlID + " in(" + (str) + ")");
    
            }
            catch { }
            finally
            {
            }
        }
    
        protected void gvBrowser_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
        {
            if (e.Parameters != "Q")
            {
                Common.SaveFlag = false;
                if (ViewState["mode"] == null) return;
                if ((ViewState["mode"].ToString() == "Customer") && (e.Parameters != "CUST"))
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Customer", "CustomerID", e.Parameters);
                    LoadCustomerData();
                }
                else if (ViewState["mode"].ToString() == "Budget")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Budget", "BudgetID", e.Parameters);
                    LoadBudgetData();
                }
                else if (ViewState["mode"].ToString() == "Company")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("CompanyInfo", "CompanyInfoID", e.Parameters);
                    LoadCompanyData();
                }
                else if (ViewState["mode"].ToString() == "Access Group")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("AccessGroup", "AccessGroupID", e.Parameters);
                    LoadAccessGroupData();
                }
                else if (ViewState["mode"].ToString() == "Access Rights")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("AccessRights", "AccessGroupID", e.Parameters);
                    //LoadAccessRightData();
                    LoadLoginUserData();  //15072009
                }
                else if (ViewState["mode"].ToString() == "Login Users")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("[User]", "UserID", e.Parameters);
                    LoadLoginUserData();
                }
                else if (ViewState["mode"].ToString() == "Product Data")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Product", "ProductID", e.Parameters);
                    LoadProductData();
                }
                else if (ViewState["mode"].ToString() == "Designer,Sales Persons and Fitter")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Personnel", "PersonnelID", e.Parameters);
                    LoadPersonnelData();
                }
                else if (ViewState["mode"].ToString() == "Process")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Process", "ProcessID", e.Parameters);
                    LoadProcessData();
                }
                else if (ViewState["mode"].ToString() == "Concessions")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Concession", "ConcessionID", e.Parameters);
                    LoadConcessionData();
                }
                else if (ViewState["mode"].ToString() == "Finance Company")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Finance", "FinanceID", e.Parameters);
                    LoadFinanceCompanyData();
                }
                else if (ViewState["mode"].ToString() == "Media")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Media", "MediaID", e.Parameters);
                    LoadMediaData();
                }
                else if (ViewState["mode"].ToString() == "Materials")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Material", "MaterialID", e.Parameters);
                    LoadMaterialsData();
                }
                else if (ViewState["mode"].ToString() == "Manufacturer")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("MANUFACTURER", "ManufacturerID", e.Parameters);
                    LoadManufacturerData();
                }
                else if (ViewState["mode"].ToString() == "Letter Template")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("TemplateLetter", "TemplateLetterID", e.Parameters);
                    LoadLetterTemplateData();
                }
    
                else if (ViewState["mode"].ToString() == "Enquiry")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfEnquiryGrid("Enquiry", "EnquiryID", e.Parameters);
                    LoadEnquiryData();
                }
                else if (ViewState["mode"].ToString() == "Sales Quotation")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                    {
                        DeleteRowsOfGrid("[ORDER]", "OrderID", e.Parameters);
                        DeleteRowsOfGrid("[JoBOrder]", "OrderID", e.Parameters);
                    }
                    LoadSalesQuotationData();
                }
                else if (ViewState["mode"].ToString() == "Retail Sales")
                {
    
                    LoadRetailSalesData();
                }
                else if (ViewState["mode"].ToString() == "Generate Trade Order")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                    {
                        DeleteRowsOfGrid("[ORDER]", "OrderID", e.Parameters);
                        DeleteRowsOfGrid("[JoBOrder]", "OrderID", e.Parameters);
                        objDbManip.ExecNonQuery("Delete from JobOrderDetails where JobOrderID Not In(Select JobOrderID from JobOrder)");
                        objDbManip.ExecNonQuery("delete from paymenthdr where orderid not in (select orderid from [order] where ordertype='T')");
                        objDbManip.ExecNonQuery("delete from paymentdtl where paymenthdrid not in (select paymenthdrID from paymenthdr)");
                    }
                    LoadGenerateTradeOrderData();
                }
                else if (ViewState["mode"].ToString() == "Generate Retail Order")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                    {
                        DeleteRowsOfGrid("[ORDER]", "OrderID", e.Parameters);
                        DeleteRowsOfGrid("[JoBOrder]", "OrderID", e.Parameters);
                        objDbManip.ExecNonQuery("Delete from JobOrderDetails where JobOrderID Not In(Select JobOrderID from JobOrder)");
                        objDbManip.ExecNonQuery("update paymenthdr set orderid=null,orderdate=null where orderid not in (select orderid from [order] where ordertype='R')");
                    }
                    LoadGenerateRetailOrderData();
                }
                else if (ViewState["mode"].ToString() == "Production Advice")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("ProductionAdvice", "ProductionAdviceID", e.Parameters);
                    LoadProductionAdviceData();
                }
                else if (ViewState["mode"].ToString() == "Generate Trade Invoice")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("InvoiceHdr", "InvoiceHdrID", e.Parameters);
                    LoadGenerateTradeInvoiceData();
                }
                else if (ViewState["mode"].ToString() == "Generate Retail Invoice")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("InvoiceHdr", "InvoiceHdrID", e.Parameters);
                    LoadGenerateRetailInvoiceData();
    
                }
                else if (ViewState["mode"].ToString() == "Despatch Job")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                       // DeleteRowsOfGrid("JobOrder", "JobOrderID", e.Parameters);
                        DeleteRowsOfDespatch("JobOrder", "JobOrderID", e.Parameters);
                    LoadDespatchJob();
                }
                else if (ViewState["mode"].ToString() == "Delivery Job")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        // DeleteRowsOfGrid("JobOrder", "JobOrderID", e.Parameters);
                        DeleteRowsOfDelivery("JobOrder", "JobOrderID", e.Parameters);
                        //DeleteRowsOfDespatch("JobOrder", "JobOrderID", e.Parameters);
                    LoadDeliveryJob();
                }
                else if (ViewState["mode"].ToString() == "Update Fitter Sheet")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("FitSheetHdr", "FitSheetHdrID", e.Parameters);
                    LoadUpdateFitterSheetData();
                }
                else if (ViewState["mode"].ToString() == "Warranty Certificate")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("WarrantyCertificate", "WarrantyCertificateID", e.Parameters);
                    LoadWarrantyCertificateData();
                }
                else if (ViewState["mode"].ToString() == "Records Advertisement Detail")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("ADSHdr", "ADSHdrID", e.Parameters);
                    LoadAdvertisementDtlData();
                }
                else if (ViewState["mode"].ToString() == "Make Payment to Media")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("PaymentHdrID", "PaymentHdrID", e.Parameters);
                    LoadMediaDtlData();
                }
                else if (ViewState["mode"].ToString() == "Record Confirmation of Payment")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("Media", "ADSHdrID", e.Parameters);
                    LoadPaymentConfirmationDtlData();
                }
                else if (ViewState["mode"].ToString() == "Calculate Commission and Wages")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("[AccessGroup]", "PersonnelId", e.Parameters);
                    LoadCalculateCommissionWagesDtlData();
                }
                else if (ViewState["mode"].ToString() == "Manage Absent")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("ManageAbsentHdr", "ManageAbsentHdrID", e.Parameters);
                    LoadManageAbsentDtlData();
    
                }
                else if (ViewState["mode"].ToString() == "Receipt")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                    {
                        //DeleteRowsOfGrid("PaymentHdr", "PaymentHdrID", e.Parameters);
                        //DeleteRowsOfGrid("PaymentDtl", "PaymentHdrID", e.Parameters);
                    }
                    LoadPaymentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        DeleteRowsOfGrid("ReceiptHdr", "ReceiptHdrID", e.Parameters);
                    LoadReceiptDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment To Designer")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        LoadDesignerPaymentDtlData();
                }
                else if (ViewState["mode"].ToString() == "Payment To Fitter")
                {
                    if ((e.Parameters != "") && (e.Parameters != "Q"))
                        LoadFitterPaymentDtlData();
                }
            }
            else if (e.Parameters == "Q")
            {
                try
                {
                    if (ViewState["mode"].ToString() == "Sales Quotation")
                    {
                        int i = gvBrowser.FocusedRowIndex;
                        if (i >= 0)
                        {
                            string str1 = gvBrowser.GetRowValues(i, "OrderID").ToString();
                            if (!IsOrderCreated(Convert.ToInt32(str1)))
                                ConvertSalesQuotationData(str1);
                        }
                    }
                }
                catch { return; }
            }
    
        }
    
        private bool IsOrderCreated(int z)
        {
            DataTable dtbl = null;
            try
            {
                dtbl = objDbManip.GetDataTable("Select count(OrderID) from [Order] where OrderNo is not null and OrderNo<>'' and OrderID=" + z + "");
                if (Convert.ToInt16(dtbl.Rows[0][0].ToString()) > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            finally
            {
                if(dtbl!=null) dtbl.Dispose();
            }
        }
    
        private void ConvertSalesQuotationData(string str)
        {
            DataTable dt1 = null;
            DateTime dtOrderDate = Convert.ToDateTime(null);
            string TotalWithVat = "";
            string CustomerID = "";
            DataSet ds = null;
            DataTable DtDialog = null;
            DataTable dt = null;
            ds = new DataSet();
    
    
            SqlConnection sqlcon = new SqlConnection(strConnection);
            sqlcon.Open();
    
            string str1 = "Update [Order] set OrderNo=@OrderNo,OrderDate=@OrderDate,Status='Raised' where OrderId=@OrderId";
            SqlCommand SQLComm = new SqlCommand();
            try
            {
                SQLComm.CommandText = str1;
                SQLComm.Parameters.Add("@OrderNo", SqlDbType.VarChar);
                SQLComm.Parameters.Add("@OrderDate", SqlDbType.DateTime);
                SQLComm.Parameters.Add("@OrderId", SqlDbType.Int);
    
                SQLComm.Parameters["@OrderNo"].Value = str.PadLeft(10, '0');
                SQLComm.Parameters["@OrderDate"].Value = System.DateTime.Now;
                SQLComm.Parameters["@OrderId"].Value = Int32.Parse(str);
                SQLComm.Connection = sqlcon;
                SQLComm.ExecuteNonQuery();
                SQLComm.Parameters.Clear();
    
                SQLComm.CommandText = "Update [JobOrder] set OrderId=@OrderId where QuotationID=@QuotationID";
                SQLComm.Parameters.Add("@OrderId", SqlDbType.Int);
                SQLComm.Parameters.Add("@QuotationID", SqlDbType.Int);
    
                SQLComm.Parameters["@OrderId"].Value = Int32.Parse(str);
                SQLComm.Parameters["@QuotationID"].Value = Int32.Parse(str);
                SQLComm.ExecuteNonQuery();
                SQLComm.Parameters.Clear();
    
                
    
                dt1 = objDbManip.GetDataTable("Select * from [Order] where OrderId=" + Int32.Parse(str));
    
                dtOrderDate = Convert.ToDateTime(dt1.Rows[0]["OrderDate"]);
                TotalWithVat = dt1.Rows[0]["SaleValue"].ToString();
                CustomerID = dt1.Rows[0]["CustomerID"].ToString();
    
                
                string sql = "Select * from PaymentHdr where PaymentHdrID=-1";
                string sql1 = "select * from PaymentDtl where PaymentDtlID=-1";
    
                DtDialog = objDbManip.GetDataTable(sql);
                dt = objDbManip.GetDataTable(sql1);
    
                DtDialog.TableName = "PaymentHdr";
                dt.TableName = "PaymentDtl";
    
               
                DtDialog.Rows.Add(new object[] { 0, Int32.Parse(str), dtOrderDate.Date, Convert.ToDecimal(TotalWithVat), Convert.ToDecimal(0), "", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "", Common.GetCurrentUser(), DateTime.Now, Common.GetCurrentUser(), DateTime.Now, Convert.ToInt32(CustomerID) });
    
                dt.Rows.Add(new object[] { 0, 0, 1, 1, "", dtOrderDate.Date, "", Convert.ToDecimal(TotalWithVat), "Y", 0, 0 });
    
                ds.Tables.Add(DtDialog.Copy());
                ds.Tables.Add(dt.Copy());
    
                ds.Tables[0].TableName = "LLOYDS_PaymentHdr";
                ds.Tables[1].TableName = "LLOYDS_PaymentDtl";
    
                NameValuePairList nvplStoredProcParamWithValues = new NameValuePairList();
                nvplStoredProcParamWithValues.Add(new NameValuePair("xmlData", ds.GetXml()));
    
                objDbManip.ExecSP("LLOYDS_SAVEPayment", nvplStoredProcParamWithValues);
    
            }
            catch (Exception Ex)
            {
                
            }
            finally
            {
                if (DtDialog != null) DtDialog.Dispose();
                if (dt != null) dt.Dispose();
                ds.Dispose();
    
                sqlcon.Close();
                sqlcon.Dispose();
                SQLComm.Dispose();
            }
        }
    
        private void SavePaymentData(string strOrderID, string strDate, string strCustName)
        {
            DataSet ds = new DataSet();
            DataTable DtDialog = null;
            DataTable dt = null;
    
            try
            {
                string sql = " Select * from PaymentHdr where PaymentHdrID=-1";
                string sql1 = " select * from PaymentDtl where PaymentDtlID=-1";
    
                DtDialog = objDbManip.GetDataTable(sql);
                DtDialog.TableName = "PaymentHdr";
                dt = objDbManip.GetDataTable(sql1);
                dt.TableName = "PaymentDtl";
    
                
                int i = Convert.ToInt32(strCustName);
    
                DtDialog.Rows.Add(new object[] { 0, Convert.ToInt32(strOrderID), Convert.ToDateTime(strDate), Convert.ToDecimal(0), Convert.ToDecimal(0), 0, 0, "", Common.GetCurrentUser(), DateTime.Now, Common.GetCurrentUser(), DateTime.Now, i });
    
                dt.Rows.Add(new object[] { 0, 0, 1, 1, "", null, "", 0, "N", 0, 0 });
                dt.Rows.Add(new object[] { 0, 0, 2, 1, "", null, "", 0, "N", 0, 0 });
                dt.Rows.Add(new object[] { 0, 0, 3, 1, "", null, "", 0, "N", 0, 0 });
                dt.Rows.Add(new object[] { 0, 0, 4, 1, "", null, "", 0, "N", 0, 0 });
    
                ds.Tables.Add(DtDialog.Copy());
                ds.Tables.Add(dt.Copy());
    
                ds.Tables[0].TableName = "LLOYDS_PaymentHdr";
                ds.Tables[1].TableName = "LLOYDS_PaymentDtl";
    
                NameValuePairList nvplStoredProcParamWithValues = new NameValuePairList();
                nvplStoredProcParamWithValues.Add(new NameValuePair("xmlData", ds.GetXml()));
                objDbManip.ExecSP("LLOYDS_SAVEPayment", nvplStoredProcParamWithValues);
            }
            finally
            {
                if(DtDialog!=null) DtDialog.Dispose();
                if(dt!=null) dt.Dispose();
                ds.Dispose();
            }
        }
    
        protected void gvBrowser_HtmlRowCreated1(object sender, ASPxGridViewTableRowEventArgs e)
        {
            e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='gold';");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='white';");
        }
    
        public void OpenNewWindow(string url)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "newWindow", String.Format("<script>window.open('{0}');</script>", url));
        }
    
        private void ExportJobXML(int intJobOrderID,int intOrderID,DataSet ds)
        {
            SqlParameter sqlparam = null;
            SqlParameter[] arrParam = null;
            DataTable dtFill = null;
            try
            {
                DataTable dt = ds.Tables["OrderItem"];
    
                sqlparam = new SqlParameter("@JobOrderID", SqlDbType.Int);
                arrParam = new SqlParameter[1];
    
                sqlparam.Value = intJobOrderID;
                arrParam[0] = sqlparam;
    
                dtFill = objDbManip.GetDataTableFromSp("ExportJobXML", arrParam);
    
                foreach (DataRow dr in dtFill.Rows)
                {
                    DataRow drow = dt.NewRow();
    
                    drow["OrderItemID"] = dr["OrderItemID"];
                    drow["AlphacamJobID"] = 0;
                    drow["NestedSheetID"] = 0;
                    drow["JobID"] = dr["JobID"];
                    drow["ProductCode"] = dr["ProductCode"];
                    drow["OrderItemNumber"] = dr["OrderItemNumber"];
                    drow["OrderItemDescription"] = dr["OrderItemDescription"];
                    drow["Height"] = dr["Height"];
                    drow["Width"] = dr["Width"];
                    drow["Quantity"] = dr["Quantity"];
                    drow["RoutStyle"] = dr["RoutStyle"];
                    drow["HingeStyle"] = dr["HingeStyle"];
                    drow["ProductOptionsXml"] = "";
                    drow["OptionalParameters"] = "";
                    drow["DrawingFile"] = "";
                    drow["MacroName"] = "";
                    drow["RotationAngle"] = 0;
                    drow["IsMirrorable"] = 0;
                    drow["AlphacamLinkModeFlag"] = "";
    
                    dt.Rows.Add(drow);
                }
    
                if (dt.Rows.Count <= 0) return;
    
                string spath = Server.MapPath(""); //ConfigurationManager.AppSettings["JobXMLPath"].ToString();
    
                if (!Directory.Exists(spath))
                    Directory.CreateDirectory(spath);
    
                string strFileName = intOrderID.ToString() + "_" + intJobOrderID.ToString() + ".xml";
    
                if (File.Exists(spath +"\\"+ strFileName))
                    File.Delete(spath + "\\" + strFileName);
    
                dt.WriteXml(spath + "\\" + strFileName);
    
                CreateFile(strFileName, FileType.JobXML);
            }
            finally
            {
                dtFill.Dispose();
            }
        }
    
        private void OpenRetailOrderWindow(string index, string Mode)
        {
            //string[] arrId = new String[gvBrowser.GetSelectedFieldValues("OrderID").Count];  //05082009
            int o = -1;
            int t = 0;
            Session["OrderID"] = -1;  //21072009;
            try
            {
                if ((Mode == "O") || (Mode == "A"))
                    o = Convert.ToInt32(gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "EnquiryNo" }));
                else if ((Mode == "E") || (Mode == "X") || (Mode == "D"))   //20072009 //D added
                {
                    o = Convert.ToInt32(gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "OrderID" }));
                   //arrId  = (gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "OrderID" }));
                   //string s1= (String)(gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "OrderID" }));
                }
            }
            catch
            {
                o = -1;
                return;
            }
            if (o == -1) return;
            string s = o.ToString();
            
    
            Session["OrderID"] = s;  //21072009
    
    
            if (Mode == "X")
            {
                //Job XML
                /* rem 20072009
                string strPath = Server.MapPath("../");
    
                
                DataSet ds = null;
                try
                {
                    
                    ds = new DataSet();
                    ds.ReadXmlSchema(strPath + "jobxmlschema.xml");
    
                    DataTable dtJobOrder = objDbManip.GetDataTable("Select joborderid from joborder where orderid=" + Convert.ToInt32(o));
                    if (dtJobOrder.Rows.Count <= 0) return;
    
                    foreach (DataRow dr in dtJobOrder.Rows)
                    {
                        ExportJobXML(Convert.ToInt32(dr["joborderid"].ToString()), Convert.ToInt32(o.ToString()),ds);
                    }
                    
    
                   
                }
                finally
                {
                    ds.Dispose();
                }
                     * */
                
                Export_to_XML(o);  //20072009
    
             }
    
             //20072009
             if (Mode == "D")
             {
                 Export_to_DAT(o); 
             }
             //20072009
    
             else if (Mode == "E")
             {
                 //Ecabinet
                 object ParentSerialNo = null;
                 int intParentserialNo = 0;
    
                 object ParentCurvedSerialNo = null;
                 int intParentCurvedSerialNo = 0;
    
                 object ParentStraightSerialNo = null;
                 int intParentStraightSerialNo = 0;
    
                 object JobOrderDetailsID = null;
                 int intJobOrderDetailsID = 0;
    
    
                 int JID = 0;
                 string ProductName = string.Empty;
    
                 DataTable dt = null;
                 int JobOrderID = 0;
    
                 //DataTable dtJobOrder = objDbManip.GetDataTable("Select j.joborderid,j.jobno,Jd.jobOrderDetailsID from joborder j,JobOrderDetails Jd where j.JobOrderID=Jd.joborderid and j.orderid=" + Convert.ToInt32(o));
                 DataTable dtJobOrder = objDbManip.GetDataTable("Select j.Orderid,j.joborderid,j.JobNo from joborder j where j.orderid=" + Convert.ToInt32(o));
                 string spath = Server.MapPath(""); //ConfigurationManager.AppSettings["EcabinetPath"].ToString();
    
                 try
                 {
                     if (!Directory.Exists(spath))
                         Directory.CreateDirectory(spath);
    
                     foreach (DataRow dr in dtJobOrder.Rows)
                     {
                         SqlParameter sqlparam = new SqlParameter("@orderid", SqlDbType.Int);
                         SqlParameter sqlparam1 = new SqlParameter("@joborderid", SqlDbType.Int);
                         SqlParameter[] arrParam = new SqlParameter[2];
    
                         dt = null;
    
                         sqlparam.Value = Convert.ToInt32(o);
                         arrParam[0] = sqlparam;
                         sqlparam1.Value = Convert.ToInt32(dr["joborderid"].ToString());
                         arrParam[1] = sqlparam1;
    
                         JobOrderID = Convert.ToInt32(dr["joborderid"].ToString());
    
                         dt = objDbManip.GetDataTableFromSp("GET_ECABINET_DATA_TEST", arrParam);
    
    
                         int id = 0;
                         string strLH = string.Empty;
                         string strRH = string.Empty;
                         string strOptionName = string.Empty;
    
                         string[] strArr = null;
                         string strAllOptionName = string.Empty;
                         int intindex = 0;
                         int DefID = 0;
                         object objDescription = null;
                         string strDescription = "";
                         string strProductName = string.Empty;
                         DataTable dtParentSerialNo = null;
                         DataTable dtParentStraight = null;
                         DataTable dtParentCurved = null;
    
    
                         foreach (DataRow drw in dt.Rows)
                         {
                             JID = Convert.ToInt32(drw["Joborderdetailsid"].ToString());
    
                             dtParentSerialNo = objDbManip.GetDataTable("select ParentSerialNo,JobOrderDetailsID  from JOBORDERDETAILS  where JobOrderId=" + JobOrderID + " And ProductName Like '%Back%'");
                             foreach (DataRow drp in dtParentSerialNo.Rows)
                             {
                                 ParentSerialNo = drp["ParentSerialNo"];
    
                                 if (ParentSerialNo != null)
                                 {
                                     intParentserialNo = Convert.ToInt32(ParentSerialNo);
                                 }
                                 else
                                 {
                                     intParentserialNo = 0;
                                 }
    
                                 JobOrderDetailsID = objDbManip.ExecuteScalar("select JobOrderDetailsID from JOBORDERDETAILS where SerialNo=" + intParentserialNo + " and JobOrderId=" + JobOrderID);
                                 if (JobOrderDetailsID != null)
                                 {
                                     intJobOrderDetailsID = Convert.ToInt32(JobOrderDetailsID);
                                 }
                                 else
                                 {
                                     intJobOrderDetailsID = 0;
                                 }
                                 if (JID == intJobOrderDetailsID)
                                 {
    
                                     DataRow[] record = dt.Select("JobOrderDetailsID=" + intJobOrderDetailsID);
                                     if (record[0].ItemArray[8].ToString() != "0054 - ")
                                     {
                                         ProductName = record[0]["ProductName"] + "-Back";
                                     }
                                     else
                                     {
                                         ProductName = record[0]["ProductName"].ToString();
                                     }
                                     record[0]["ProductName"] = ProductName;
    
                                 }
                             }
    
                             dtParentCurved = objDbManip.GetDataTable("select ParentSerialNo,JobOrderDetailsID  from JOBORDERDETAILS  where JobOrderId=" + JobOrderID + " And ProductName Like '%Curved%'");
                             foreach (DataRow dpc in dtParentCurved.Rows)
                             {
                                 ParentCurvedSerialNo = dpc["ParentSerialNo"];
    
                                 if (ParentCurvedSerialNo != null)
                                 {
                                     intParentCurvedSerialNo = Convert.ToInt32(ParentCurvedSerialNo);
                                 }
                                 else
                                 {
                                     intParentCurvedSerialNo = 0;
                                 }
                                 JobOrderDetailsID = objDbManip.ExecuteScalar("select JobOrderDetailsID from JOBORDERDETAILS where SerialNo=" + intParentCurvedSerialNo + " and JobOrderId=" + JobOrderID);
                                 if (JobOrderDetailsID != null)
                                 {
                                     intJobOrderDetailsID = Convert.ToInt32(JobOrderDetailsID);
                                 }
                                 else
                                 {
                                     intJobOrderDetailsID = 0;
                                 }
                                 if (JID == intJobOrderDetailsID)
                                 {
                                     string[] PName = null;
                                     DataRow[] record = dt.Select("JobOrderDetailsID=" + intJobOrderDetailsID);
    
                                     PName = Convert.ToString(record[0]["ProductName"]).Split('-');
                                     ProductName = "";
                                     for (int i = 0; i < PName.Length; i++)
                                     {
                                         if (PName[i] == PName[1])
                                             PName[1] = "-Curved Pelmet-" + PName[1];
                                         ProductName = Convert.ToString(ProductName + PName[i]);//+ "-Curved Pelmet" + PName[1].ToString() + PName[2];
    
                                         record[0]["ProductName"] = ProductName;
                                     }
    
                                 }
                             }
    
                             dtParentStraight = objDbManip.GetDataTable("select ParentSerialNo,JobOrderDetailsID  from JOBORDERDETAILS  where JobOrderId=" + JobOrderID + " And ProductName Like '%Straight%'");
                             foreach (DataRow dps in dtParentStraight.Rows)
                             {
                                 ParentStraightSerialNo = dps["ParentSerialNo"];
    
                                 if (ParentStraightSerialNo != null)
                                 {
                                     intParentStraightSerialNo = Convert.ToInt32(ParentStraightSerialNo);
                                 }
                                 else
                                 {
                                     intParentStraightSerialNo = 0;
                                 }
    
                                 JobOrderDetailsID = objDbManip.ExecuteScalar("select JobOrderDetailsID from JOBORDERDETAILS where SerialNo=" + intParentStraightSerialNo + " and JobOrderId=" + JobOrderID);
                                 if (JobOrderDetailsID != null)
                                 {
                                     intJobOrderDetailsID = Convert.ToInt32(JobOrderDetailsID);
                                 }
                                 else
                                 {
                                     intJobOrderDetailsID = 0;
                                 }
                                 if (JID == intJobOrderDetailsID)
                                 {
                                     string[] PName = null;
                                     DataRow[] record = dt.Select("JobOrderDetailsID=" + intJobOrderDetailsID);
    
                                     PName = Convert.ToString(record[0]["ProductName"]).Split('-');
                                     ProductName = "";
                                     for (int i = 0; i < PName.Length; i++)
                                     {
                                         if (PName[i] == PName[1])
                                             PName[1] = "-Straight Pelmet-" + PName[1];
                                         ProductName = Convert.ToString(ProductName + " " + PName[i]);//+ "-Curved Pelmet" + PName[1].ToString() + PName[2];
    
                                         record[0]["ProductName"] = ProductName;
                                     }
    
                                     //ProductName = "-Straight Pelmet" + PName[1].ToString();
                                     //record[0]["ProductName"] = ProductName;
    
                                 }
                             }
    
    
    
    
                             intindex = 0;
                             DefID = 0;
                             strAllOptionName = "";
                             strOptionName = "";
                             id = 0;
                             strProductName = "";
    
    
    
                             // id = Convert.ToInt32(drw["Joborderdetailsid"].ToString());
    
                             DataTable dtbl = objDbManip.GetDataTable("select optionvalues,lh,rh,productname from joborderdetails where joborderdetailsid=" + JID);
                             if (dtbl.Rows[0]["optionvalues"] != DBNull.Value)
                                 strOptionName = dtbl.Rows[0]["optionvalues"].ToString();
    
    
                             strLH = dtbl.Rows[0]["LH"].ToString();
                             strRH = dtbl.Rows[0]["RH"].ToString();
                             strProductName = Common.DecodeXMLChar(dtbl.Rows[0]["productname"].ToString());
    
                             // strProductName = strProductName.Replace('', ' ');
                             //strProductName = strProductName.Replace("&#176", "°");
                             strProductName = strProductName.Replace("°", "&#176");
                             //The Word Back Inclusion 
    
    
                             if (strRH.ToString() == "Y")
                             {
                                 strRH = "RH";
                                 strLH = "";
                             }
                             else if (strLH.ToString() == "Y")
                             {
                                 strLH = "LH";
                                 strRH = "";
                             }
                             else if ((strRH.ToString() == "N") && (strLH.ToString() == "N"))
                             {
                                 strRH = "";
                                 strLH = "";
                             }
    
                             drw["ProductName"] = drw["ProductName"].ToString() + " " + strLH + strRH;
    
                             strAllOptionName = "";
                             strArr = strOptionName.Split(';');
    
                             if (strArr[0].Length > 0)
                             {
                                 for (int y = 0; y <= strArr.Length - 1; y++)
                                 {
                                     intindex = strArr[y].IndexOf(':');
                                     DefID = Convert.ToInt32(strArr[y].Substring(0, intindex));
                                     string[] Desc = strArr[y].Split(':');
                                     if (Desc[1].Length > 0)
                                     {
                                         objDescription = objDbManip.ExecuteScalar("select Description from OptionScriptDef where ID=" + DefID);
                                         if (objDescription != null)
                                         {
                                             strDescription = objDescription.ToString();
                                             if (strDescription == "Edging")
                                                 strDescription = "E";
                                             else
                                                 if (strDescription == "Slotting")
                                                     strDescription = "SLOT";
                                         }
                                     }
                                     else
                                     {
                                         strDescription = "";
                                     }
                                     strAllOptionName = strDescription.ToUpper() + " " + strArr[y].Substring(intindex + 1) + " " + strAllOptionName.Trim();
                                 }
                             }
                             //}
                             // }
                             // }
                             // }
                             strAllOptionName = strAllOptionName.Trim() + " " + strLH + strRH;
                             strAllOptionName = strAllOptionName.Replace("(TRADE=0)", "");
                             drw["NewName"] = drw["NewName"].ToString().Replace('/', ' ') + strProductName.Trim().Replace('/', ' ') + " " + strAllOptionName.Trim().Replace('/', ' ');
    
    
    
                         }
                         //strAllOptionName = strAllOptionName.Trim() + " " + strLH + strRH;
                         //strAllOptionName = strAllOptionName.Replace("(TRADE=0)", "");
                         //drw["NewName"] = drw["NewName"].ToString().Replace('/', ' ') + strProductName.Trim() + " " + strAllOptionName.Trim().Replace("/", " ");
    
    
    
                         //strProductName = objDbManip.ExecuteScalar("");
    
                         //int id = 0;
                         //string strLH = string.Empty;
                         //string strRH = string.Empty;
                         //string strOptionName = string.Empty;
    
                         //string [] strArr =null;
                         //string strAllOptionName = string.Empty;
                         //int intindex = 0;
                         //int DefID = 0;
                         //object objDescription = null;
                         //string strDescription = "";
                         //string strProductName = string.Empty;
    
    
                         //foreach (DataRow drw in dt.Rows)
                         //{
    
                         //}
    
                         if (dt.Rows.Count == 0) continue;
    
                         d = new GridviewToExcel.export2Excel();
                         string sfile = spath + "\\" + o.ToString().PadLeft(10, '0') + "_" + dr["jobno"].ToString() + ".xls";
    
                         //DROP THE IST COLUMN
                         dt.Columns.RemoveAt(0);
                         d.ExportToExcel(dt.DefaultView, sfile);
                         d = null;
    
                         CreateFile(o.ToString().PadLeft(10, '0') + "_" + dr["jobno"].ToString() + ".xls",
                            FileType.ECabinet);
    
                         object countjobno = null;
                         countjobno = objDbManip.ExecuteScalar("Select Count(JobNo) from JobOrder where OrderID=" + Convert.ToInt32(o));
                         if (Convert.ToInt32(countjobno) == Convert.ToInt32(dr["jobno"]))
                             break;
    
                     }
                 }
                 catch (Exception ex)
                 {
                     lblCaption.Text = ex.Message;
                 }
                 finally
                 {
                     dtJobOrder.Dispose();
                     dtJobOrder = null;
                 }
             }
             else if (Mode == "O")
             {
                 if (Convert.ToInt32(objDbManip.GetDataSet("select count(*) from [order] where enquiryno=" + s + " and isjob=1").Tables[0].Rows[0][0].ToString()) > 0) return;
    
                 DataSet dt = null;
                 dt = objDbManip.GetDataSet("Select CustomerID,DesignerID,FitterID,ConcessionID from Paymenthdr where EnquiryNo=" + s + "");
                 string CustomerID = string.Empty;
                 string DesignerID = string.Empty;
                 string FitterID = string.Empty;
                 string ConcessionID = string.Empty;
                 if (dt.Tables[0].Rows.Count > 0)
                 {
                     CustomerID = dt.Tables[0].Rows[0][0].ToString();
                     DesignerID = dt.Tables[0].Rows[0][1].ToString();
                     FitterID = dt.Tables[0].Rows[0][2].ToString();
                     ConcessionID = dt.Tables[0].Rows[0][3].ToString();
                 }
                 else
                     return;
    
                 dt = null;
    
                 if (s.Length <= 0) return;
    
                 try { o = Convert.ToInt32(gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "PaymentHdrID" })); }
                 catch { o = 0; }
    
                 t = Convert.ToInt32(o);
                 if (t < 0) return;
    
                 strGenerateJobUrl = "../Order/frmGenarateOrderDlg.aspx?Permission=Y,Y,Y,Y,&EQ=" + s + "&PHID=" + t + "&CID=" + CustomerID + "&DID=" + DesignerID + "&OType=R &FID=" + FitterID + "&COID=" + ConcessionID + "&','GenerateOrder','location=0,status=1,toolbar=0,menubar=0,resizable=0,dependent=0,width=1000,height=650,left=10,top=50";
             }
             else if (Mode == "A")
             {
                 object a = null;
                 object b = null;
                 object c = null;
                 object d = null;
                 object e = null;
    
                 try { e = gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "Status" }); }
                 catch { e = -1; }
                 if (e.ToString() == "Cancel")
                 {
                     return;
                 }
    
                 try { a = gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "CustomerName" }); }
                 catch { a = -1; }
                 try { b = gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "CustomerID" }); }
                 catch { b = -1; }
                 try { c = gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "Designer" }); }
                 catch { c = -1; }
                 try { d = gvBrowser.GetRowValues(Convert.ToInt16(index), new string[] { "PersonnelID" }); }
                 catch
                 {
                     d = -1;
                     return;
                 }
                 int i = Convert.ToInt32(o);
                 string k = Convert.ToString(a);
                 int j = Convert.ToInt32(b);
                 string l = string.Empty;
                 int m = 0;
                 if (c.ToString() != "")
                     l = Convert.ToString(c);
                 if (d.ToString() == "") return;
    
                 m = Convert.ToInt32(d);
    
                 if (objDbManip.GetDataSet("Select PaymentHdrID from PaymentHdr where convert(int,EnquiryNo)='" + s + "'").Tables[0].Rows.Count <= 0)
                 {
                     if (i > 0)
                     {
                         strGenerateJobUrl = "../ReceiptPayment/frmPaymentDlg.aspx?Permission=Y,Y,Y,Y,&EQ=A&EQNO=" + i + "&CID=" + j + "&CName=" + k + "&PName=" + l + "&PID=" + m + "&','GenerateJob','location=0,status=1,toolbar=0,menubar=0,resizable=0,dependent=0,width=1000,height=650,left=10,top=50";
                     }
                 }
                 else
                 {
                 }
             }
        }
    
        enum FileType { ECabinet, ProductPlanner, JobXML, DAT };  //DAT added on 20072009
        private void CreateFile(string filename, FileType ft)
        {
            // Get the full file path
            //string strFilePath = Server.MapPath("") + @"\\auto.bat";
    
            // Create the ProcessInfo object
            //System.Diagnostics.Process p = new System.Diagnostics.Process();
            //p.
            //p.Start();
            
    
            System.Diagnostics.ProcessStartInfo psi =
                new System.Diagnostics.ProcessStartInfo("cmd.exe");
            
            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.RedirectStandardInput = true;
            psi.RedirectStandardError = true;
            
            psi.WorkingDirectory = Server.MapPath("") + @"\";
    
            // Start the process
            System.Diagnostics.Process proc = System.Diagnostics.Process.Start(psi);
    
    
            // Open the batch file for reading
            //System.IO.StreamReader strm = System.IO.File.OpenText(strFilePath);
    
            // Attach the output for reading
            System.IO.StreamReader sOut = proc.StandardOutput;
    
            // Attach the in for writing
            System.IO.StreamWriter sIn = proc.StandardInput;
    
            string strDestination = string.Empty;
            if (ft == FileType.ECabinet)
                strDestination = ConfigurationManager.AppSettings["EcabinetPath"].ToString();
            else
                strDestination = ConfigurationManager.AppSettings["JobXMLPath"].ToString();
    
            strDestination = strDestination + @"\" + filename;
            sIn.WriteLine("DEL " + " \"" + strDestination + "\"");
            sIn.WriteLine("MOVE " + filename + " \"" + strDestination + "\"");
            sIn.WriteLine("DEL " + filename);
    
            sIn.WriteLine("EXIT");
    
            // Close the process
            proc.Close();
    
            // Read the sOut to a string.
            //string results = sOut.ReadToEnd().Trim();
    
    
            // Close the io Streams;
            sIn.Close();
            sOut.Close();
        }
    }
    
    
    Wednesday, June 8, 2011 6:06 AM
  • User-366017857 posted
     string strSQL = "SELECT DISTINCT E.EnquiryNo, ";
    check this EnquiryNo It is correct in db table




    This usually means your SQL statement is joining on more than one table, and doesn't know which column you're trying to retrieve. For example: 
     
    SELECT column1 
        FROM table1 
        JOIN table2 
        ON table1.column1 = table2.column1
     
    To fix this, you'll want to butter up the statement a bit: 
     
    SELECT column1 = t1.column1 
        FROM table1 t1 
        JOIN table2 t2 
        ON t1.column1 = t2.column1
    Wednesday, June 8, 2011 6:11 AM
  • User2080995897 posted

    sorry i didnt understand it? do i need to go to database and than look for string strSQL = "SELECT DISTINCT E.EnquiryNo, ";  ? and than do what thr?

    Wednesday, June 8, 2011 6:21 AM