예제 #1
0
        //Detail Indent list for closoing PAF
        public static List <indentserviceheader> IndentDetailListWRTRefPONos(string refPONos)
        {
            List <indentserviceheader> IndentList = new List <indentserviceheader>();
            indentserviceheader        ish        = new indentserviceheader();

            try
            {
                SqlConnection conn  = new SqlConnection(Login.connString);
                string        query = "select a.DocumentID,a.TemporaryNo,a.TemporaryDate,a.ReferenceInternalOrder,a.CustomerID,b.Name from WORequestHeader a , Customer b " +
                                      " where a.CustomerID = b.CustomerID and ReferenceInternalOrder like '%" + refPONos + "%'";
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ish                        = new indentserviceheader();
                    ish.DocumentID             = reader.GetString(0);
                    ish.TemporaryNo            = reader.GetInt32(1);
                    ish.TemporaryDate          = reader.GetDateTime(2);
                    ish.ReferenceInternalOrder = reader.IsDBNull(3) ? "" : reader.GetString(3);
                    ish.CustomerID             = reader.IsDBNull(4) ? "" : reader.GetString(4);
                    ish.CustomerName           = reader.IsDBNull(5) ? "" : reader.GetString(5);
                    IndentList.Add(ish);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            return(IndentList);
        }
예제 #2
0
        public static Boolean CloseIndentService(indentserviceheader ish)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update WORequestHeader set Status = 7 " +
                                   " where DocumentID='" + ish.DocumentID + "'" +
                                   " and TemporaryNo=" + ish.TemporaryNo +
                                   " and TemporaryDate='" + ish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "WORequestHeader", "", updateSQL) +
                           Main.QueryDelimiter;
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
예제 #3
0
        public Boolean ApproveIndentService(indentserviceheader ish)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update WORequestHeader set DocumentStatus=99, status=1 " +
                                   ", ApproveUser='******'" +
                                   ", commentStatus='" + ish.CommentStatus + "'" +
                                   ", WORequestNo=" + ish.WORequestNo +
                                   ", WORequestDate=convert(date, getdate())" +
                                   " where DocumentID='" + ish.DocumentID + "'" +
                                   " and TemporaryNo=" + ish.TemporaryNo +
                                   " and TemporaryDate='" + ish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "WORequestHeader", "", updateSQL) +
                           Main.QueryDelimiter;
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
예제 #4
0
        public List <indentserviceheader> getIndentServiceListView()
        {
            indentserviceheader        ish;
            List <indentserviceheader> ISHeaders = new List <indentserviceheader>();

            try
            {
                string query = "select WORequestNo,WORequestDate,CustomerID,CustomerName,ContractorReference from ViewWORequestHeader" +
                               " where DocumentID = 'INDENTSERVICE' and Status = 1 and DocumentStatus = 99  order by WORequestDate asc,WORequestNo asc";
                SqlConnection conn = new SqlConnection(Login.connString);
                SqlCommand    cmd  = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ish                     = new indentserviceheader();
                    ish.WORequestNo         = reader.GetInt32(0);
                    ish.WORequestDate       = reader.GetDateTime(1);
                    ish.CustomerID          = reader.GetString(2);
                    ish.CustomerName        = reader.GetString(3);
                    ish.ContractorReference = reader.IsDBNull(4) ? "" : reader.GetString(4);
                    ISHeaders.Add(ish);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying Indent Service Header Details");
            }
            return(ISHeaders);
        }
예제 #5
0
        public static List <indentservicedetail> getIndentServiceDetails(indentserviceheader ish)
        {
            indentservicedetail        isd;
            List <indentservicedetail> ISDetail = new List <indentservicedetail>();

            try
            {
                string        query = "";
                SqlConnection conn  = new SqlConnection(Login.connString);
                query = "select a.RowID,a.DocumentID,a.TemporaryNo, a.TemporaryDate,a.StockItemID,b.Name as Description,a.WorkDescription,a.WorkLocation, " +
                        "a.Quantity,a.Price,a.Tax,a.WarrantyDays,a.TaxDetails,a.TaxCode,a.POItemReferenceNo " +
                        "from WORequestDetail a , ServiceItem b where a.StockItemID = b.ServiceItemID " +
                        " and a.DocumentID='" + ish.DocumentID + "'" +
                        " and a.TemporaryNo=" + ish.TemporaryNo +
                        " and a.TemporaryDate='" + ish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    isd                 = new indentservicedetail();
                    isd.RowID           = reader.GetInt32(0);
                    isd.DocumentID      = reader.GetString(1);
                    isd.TemporaryNo     = reader.GetInt32(2);
                    isd.TemporaryDate   = reader.GetDateTime(3).Date;
                    isd.StockItemID     = reader.IsDBNull(4) ? "" : reader.GetString(4);
                    isd.Description     = reader.IsDBNull(5) ? "" : reader.GetString(5);
                    isd.WorkDescription = reader.GetString(6);
                    isd.WorkLocation    = reader.GetString(7);
                    isd.Quantity        = reader.GetDouble(8);
                    isd.Rate            = reader.GetDouble(9); //Price INdent
                    isd.Tax             = reader.GetDouble(10);
                    isd.WarrantyDays    = reader.GetInt32(11);
                    isd.TaxDetails      = reader.GetString(12);
                    isd.TaxCode         = reader.IsDBNull(13) ? "" : reader.GetString(13);
                    isd.POItemRefNo     = reader.IsDBNull(14) ? 0 : reader.GetInt32(14); //PO Item Row ID
                    ISDetail.Add(isd);
                }
                conn.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Error querying Work Order Details");
            }
            return(ISDetail);
        }
예제 #6
0
        public indentserviceheader getInvdentServiceForWO(int No, DateTime date)
        {
            indentserviceheader ish = new indentserviceheader();

            try
            {
                string query = "select a.RowID, a.DocumentID, a.DocumentName,a.TemporaryNo,a.TemporaryDate," +
                               " a.WORequestNo,a.WORequestDate,a.ReferenceInternalOrder,a.CustomerID,a.CustomerName,a.CurrencyID,a.CurrencyName,a.StartDate,a.TargetDate,a.PaymentTerms,a.PaymentMode," +
                               " a.ServiceValue,a.TaxAmount,a.TotalAmount,a.Remarks, " +
                               " a.Status,a.DocumentStatus,a.CreateTime,a.CreateUser,a.ForwardUser,a.ApproveUser,a.CreatorName,a.ForwarderName,a.ApproverName,a.CommentStatus,a.ForwarderList " +
                               " ,a.ExchangeRate,a.ServiceValueINR,a.TaxAmountINR,a.TotalAmountINR,b.NoFound,a.ContractorReference from ViewWORequestHeader a left outer join " +
                               " (select DocumentID,WORequestNo,WORequestDate,COUNT(*) as NoFound from WOHeader where Status = 1 and DocumentStatus = 99 group by DocumentID,WORequestNo,WORequestDate) b on" +
                               " a.WORequestNo = b.WORequestNo and a.WORequestDate = b.WORequestDate " +
                               " where a.DocumentID = 'INDENTSERVICE' and  a.DocumentStatus = 99 and a.status = 1 and a.WORequestNo = " + No +
                               " and a.WORequestDate = '" + date.ToString("yyyy-MM-dd") + "'";
                SqlConnection conn = new SqlConnection(Login.connString);
                SqlCommand    cmd  = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    ish               = new indentserviceheader();
                    ish.RowID         = reader.GetInt32(0);
                    ish.DocumentID    = reader.GetString(1);
                    ish.DocumentName  = reader.GetString(2);
                    ish.TemporaryNo   = reader.GetInt32(3);
                    ish.TemporaryDate = reader.GetDateTime(4);
                    ish.WORequestNo   = reader.GetInt32(5);
                    if (!reader.IsDBNull(6))
                    {
                        ish.WORequestDate = reader.GetDateTime(6);
                    }
                    else
                    {
                        ish.WORequestDate = DateTime.Parse("1900-01-01");
                    }
                    ish.ReferenceInternalOrder = reader.IsDBNull(7) ? " " : reader.GetString(7);
                    ish.CustomerID             = reader.GetString(8);
                    ish.CustomerName           = reader.GetString(9);
                    ish.CurrencyID             = reader.GetString(10);
                    ish.CurrencyName           = reader.GetString(11);
                    ish.StartDate      = reader.GetDateTime(12);
                    ish.TargetDate     = reader.GetDateTime(13);
                    ish.PaymentTerms   = reader.GetString(14);
                    ish.PaymentMode    = reader.GetString(15);
                    ish.ServiceValue   = reader.GetDouble(16);
                    ish.TaxAmount      = reader.GetDouble(17);
                    ish.TotalAmount    = reader.GetDouble(18);
                    ish.Remarks        = reader.GetString(19);
                    ish.Status         = reader.GetInt32(20);
                    ish.DocumentStatus = reader.GetInt32(21);
                    ish.CreateTime     = reader.GetDateTime(22);
                    ish.CreateUser     = reader.GetString(23);
                    ish.ForwardUser    = reader.GetString(24);
                    ish.ApproveUser    = reader.GetString(25);
                    ish.CreatorName    = reader.GetString(26);
                    ish.ForwarderName  = reader.GetString(27);
                    ish.ApproverName   = reader.GetString(28);
                    if (!reader.IsDBNull(29))
                    {
                        ish.CommentStatus = reader.GetString(29);
                    }
                    else
                    {
                        ish.CommentStatus = "";
                    }
                    if (!reader.IsDBNull(30))
                    {
                        ish.ForwarderList = reader.GetString(30);
                    }
                    else
                    {
                        ish.ForwarderList = "";
                    }
                    ish.ExchangeRate        = reader.GetDecimal(31);
                    ish.ServiceValueINR     = reader.GetDouble(32);
                    ish.TaxAmountINR        = reader.GetDouble(33);
                    ish.TotalAmountINR      = reader.GetDouble(34);
                    ish.NoOfWOFound         = reader.IsDBNull(35) ? 0 : reader.GetInt32(35);
                    ish.ContractorReference = reader.IsDBNull(36) ? "" : reader.GetString(36);
                }
                else
                {
                    ish = null;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying Indent Service Header Details");
                ish = null;
            }
            return(ish);
        }
예제 #7
0
        public Boolean InsertIndentServiceHeaderAndDetail(indentserviceheader ish, List <indentservicedetail> ISDetail)
        {
            Boolean status    = true;
            string  utString  = "";
            string  updateSQL = "";

            try
            {
                ish.TemporaryNo = DocumentNumberDB.getNumber(ish.DocumentID, 1);
                if (ish.TemporaryNo <= 0)
                {
                    MessageBox.Show("Error in Creating New Number");
                    return(false);
                }
                updateSQL = "update DocumentNumber set TempNo =" + ish.TemporaryNo +
                            " where FYID='" + Main.currentFY + "' and DocumentID='" + ish.DocumentID + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) +
                           Main.QueryDelimiter;

                updateSQL = "insert into WORequestHeader " +
                            "(DocumentID,TemporaryNo,TemporaryDate,WORequestNo,WORequestDate,ReferenceInternalOrder,CustomerID,CurrencyID," +
                            "ExchangeRate,StartDate,TargetDate,PaymentTerms,PaymentMode,ServiceValue,TaxAmount,TotalAmount,ServiceValueINR,TaxAmountINR,TotalAmountINR," +
                            "Remarks,Status,DocumentStatus,CreateTime,CreateUser, CommentStatus,Comments,ContractorReference,ForwarderList)" +
                            " values (" +
                            "'" + ish.DocumentID + "'," +
                            ish.TemporaryNo + "," +
                            "'" + ish.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                            ish.WORequestNo + "," +
                            "'" + ish.WORequestDate.ToString("yyyy-MM-dd") + "'," +
                            "'" + ish.ReferenceInternalOrder + "'," +
                            "'" + ish.CustomerID + "'," +
                            "'" + ish.CurrencyID + "'," +
                            ish.ExchangeRate + "," +
                            "'" + ish.StartDate.ToString("yyyy-MM-dd") + "'," +
                            "'" + ish.TargetDate.ToString("yyyy-MM-dd") + "'," +
                            "'" + ish.PaymentTerms + "'," +
                            "'" + ish.PaymentMode + "'," +
                            ish.ServiceValue + "," +
                            ish.TaxAmount + "," +
                            ish.TotalAmount + "," +
                            ish.ServiceValueINR + "," +
                            ish.TaxAmountINR + "," +
                            ish.TotalAmountINR + "," +
                            "'" + ish.Remarks + "'," +
                            ish.Status + "," +
                            ish.DocumentStatus + "," +
                            "GETDATE()" + "," +
                            "'" + Login.userLoggedIn + "'," +
                            "'" + ish.CommentStatus + "'," +
                            "'" + ish.Comments + "'," +
                            "'" + ish.ContractorReference + "'," +
                            "'" + ish.ForwarderList + "')";

                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                updateSQL = "Delete from WORequestDetail where DocumentID='" + ish.DocumentID + "'" +
                            " and TemporaryNo=" + ish.TemporaryNo +
                            " and TemporaryDate='" + ish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("delete", "WORequestDetail", "", updateSQL) +
                           Main.QueryDelimiter;
                foreach (indentservicedetail isd in ISDetail)
                {
                    updateSQL = "insert into WORequestDetail " +
                                "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,WorkDescription,TaxCode,WorkLocation,Quantity,Price,Tax,POItemReferenceNo,WarrantyDays,TaxDetails) " +
                                "values ('" + isd.DocumentID + "'," +
                                ish.TemporaryNo + "," +
                                "'" + isd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + isd.StockItemID + "'," +
                                "'" + isd.WorkDescription + "'," +
                                "'" + isd.TaxCode + "'," +
                                "'" + isd.WorkLocation + "'," +
                                isd.Quantity + "," +
                                isd.Rate + " ," +
                                isd.Tax + "," +
                                isd.POItemRefNo + "," +
                                isd.WarrantyDays + "," +
                                "'" + isd.TaxDetails + "')";
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestDetail", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception ex)
            {
                status = false;
                MessageBox.Show("Transaction Exception Occured");
            }
            return(status);
        }
예제 #8
0
        public Boolean updateIndentServiceHeaderAndDetail(indentserviceheader ish, indentserviceheader prevish, List <indentservicedetail> ISHDetail)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update WORequestHeader set TemporaryNo = " + ish.TemporaryNo +
                                   ", TemporaryDate='" + ish.TemporaryDate.ToString("yyyy-MM-dd") +
                                   //"', WORequestNo=" + ish.WORequestNo +
                                   //", WORequestDate='" + ish.WORequestDate.ToString("yyyy-MM-dd") +
                                   "', ReferenceInternalOrder='" + ish.ReferenceInternalOrder +
                                   "', CustomerID='" + ish.CustomerID +
                                   "', CurrencyID='" + ish.CurrencyID +
                                   "', ExchangeRate=" + ish.ExchangeRate +
                                   ", StartDate='" + ish.StartDate.ToString("yyyy-MM-dd") +
                                   "', TargetDate='" + ish.TargetDate.ToString("yyyy-MM-dd") +
                                   "', PaymentTerms='" + ish.PaymentTerms +
                                   "', PaymentMode='" + ish.PaymentMode +
                                   "', ServiceValue=" + ish.ServiceValue +
                                   ",TaxAmount=" + ish.TaxAmount + "," +
                                   "TotalAmount= " + ish.TotalAmount +
                                   ", ServiceValueINR=" + ish.ServiceValueINR +
                                   ",TaxAmountINR=" + ish.TaxAmountINR + "," +
                                   "TotalAmountINR= " + ish.TotalAmountINR +
                                   ", Remarks ='" + ish.Remarks +
                                   "', ContractorReference ='" + ish.ContractorReference +
                                   "', CommentStatus='" + ish.CommentStatus +
                                   "', Comments='" + ish.Comments +
                                   "', ForwarderList='" + ish.ForwarderList + "'" +
                                   " where DocumentID='" + prevish.DocumentID + "'" +
                                   " and TemporaryNo=" + prevish.TemporaryNo +
                                   " and TemporaryDate='" + prevish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "WORequestHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                updateSQL = "Delete from WORequestDetail where DocumentID='" + prevish.DocumentID + "'" +
                            " and TemporaryNo=" + prevish.TemporaryNo +
                            " and TemporaryDate='" + prevish.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("delete", "WORequestDetail", "", updateSQL) +
                           Main.QueryDelimiter;
                foreach (indentservicedetail isd in ISHDetail)
                {
                    updateSQL = "insert into WORequestDetail " +
                                "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,WorkDescription,TaxCode,WorkLocation,Quantity,Price,Tax,POItemReferenceNo,WarrantyDays,TaxDetails) " +
                                "values ('" + isd.DocumentID + "'," +
                                isd.TemporaryNo + "," +
                                "'" + isd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + isd.StockItemID + "'," +
                                "'" + isd.WorkDescription + "'," +
                                "'" + isd.TaxCode + "'," +
                                "'" + isd.WorkLocation + "'," +
                                isd.Quantity + "," +
                                isd.Rate + " ," + //Indent Price
                                isd.Tax + "," +
                                isd.POItemRefNo + "," +
                                isd.WarrantyDays + "," +
                                "'" + isd.TaxDetails + "')";
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "WORequestDetail", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                    MessageBox.Show("Transaction Exception Occured");
                }
            }
            catch (Exception ex)
            {
                status = false;
            }
            return(status);
        }
예제 #9
0
        public Boolean validateIndentServiceHeader(indentserviceheader ish)
        {
            Boolean status = true;

            try
            {
                if (ish.DocumentID.Trim().Length == 0 || ish.DocumentID == null)
                {
                    return(false);
                }
                if (ish.ReferenceInternalOrder.Trim().Length == 0 || ish.ReferenceInternalOrder == null)
                {
                    return(false);
                }

                if (ish.CustomerID.Trim().Length == 0 || ish.CustomerID == null)
                {
                    return(false);
                }
                if (ish.CurrencyID.Trim().Length == 0 || ish.CurrencyID == null)
                {
                    return(false);
                }
                if (ish.StartDate == null)
                {
                    return(false);
                }
                if (ish.TargetDate < DateTime.Now.Date || ish.TargetDate < ish.StartDate || ish.TargetDate == null)
                {
                    return(false);
                }
                if (ish.PaymentTerms == null)
                {
                    return(false);
                }
                if (ish.PaymentMode == null)
                {
                    return(false);
                }
                if (ish.ServiceValue == 0)
                {
                    return(false);
                }
                if (ish.TotalAmount == 0)
                {
                    return(false);
                }
                if (ish.ExchangeRate == 0)
                {
                    return(false);
                }
                if (ish.Remarks.Trim().Length == 0 || ish.Remarks == null)
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
            }
            return(status);
        }
예제 #10
0
        public List <indentserviceheader> getFilteredIndentServiceHeaders(string userList, int opt, string userCommentStatusString)
        {
            indentserviceheader        ish;
            List <indentserviceheader> ISHeaders = new List <indentserviceheader>();

            try
            {
                //approved user comment status string
                string acStr = "";
                try
                {
                    acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
                }
                catch (Exception ex)
                {
                    acStr = "";
                }
                //-----
                string query1 = "select a.RowID, a.DocumentID, a.DocumentName,a.TemporaryNo,a.TemporaryDate," +
                                " a.WORequestNo,a.WORequestDate,a.ReferenceInternalOrder,a.CustomerID,a.CustomerName,a.CurrencyID,a.CurrencyName,a.StartDate,a.TargetDate,a.PaymentTerms,a.PaymentMode," +
                                " a.ServiceValue,a.TaxAmount,a.TotalAmount,a.Remarks, " +
                                " a.Status,a.DocumentStatus,a.CreateTime,a.CreateUser,a.ForwardUser,a.ApproveUser,a.CreatorName,a.ForwarderName,a.ApproverName,a.CommentStatus,a.ForwarderList " +
                                " ,a.ExchangeRate,a.ServiceValueINR,a.TaxAmountINR,a.TotalAmountINR,b.NoFound,a.ContractorReference from ViewWORequestHeader a left outer join " +
                                " (select DocumentID,WORequestNo,WORequestDate,COUNT(*) as NoFound from WOHeader where Status = 1 and DocumentStatus = 99 group by DocumentID,WORequestNo,WORequestDate) b on" +
                                " a.WORequestNo = b.WORequestNo and a.WORequestDate = b.WORequestDate " +

                                " where a.DocumentID = 'INDENTSERVICE' and ((a.forwarduser='******' and a.DocumentStatus between 2 and 98) " +
                                " or (a.createuser='******' and a.DocumentStatus=1)" +
                                " or (a.commentStatus like '%" + userCommentStatusString + "%' and a.DocumentStatus between 1 and 98)) and a.Status not in (7,98) order by a.TemporaryDate desc,a.DocumentID asc,a.TemporaryNo desc";

                string query2 = "select a.RowID, a.DocumentID, a.DocumentName,a.TemporaryNo,a.TemporaryDate," +
                                " a.WORequestNo,a.WORequestDate,a.ReferenceInternalOrder,a.CustomerID,a.CustomerName,a.CurrencyID,a.CurrencyName,a.StartDate,a.TargetDate,a.PaymentTerms,a.PaymentMode," +
                                " a.ServiceValue,a.TaxAmount,a.TotalAmount,a.Remarks, " +
                                " a.Status,a.DocumentStatus,a.CreateTime,a.CreateUser,a.ForwardUser,a.ApproveUser,a.CreatorName,a.ForwarderName,a.ApproverName,a.CommentStatus,a.ForwarderList " +
                                " ,a.ExchangeRate,a.ServiceValueINR,a.TaxAmountINR,a.TotalAmountINR,b.NoFound,a.ContractorReference from ViewWORequestHeader a left outer join " +
                                " (select DocumentID,WORequestNo,WORequestDate,COUNT(*) as NoFound from WOHeader where Status = 1 and DocumentStatus = 99 group by DocumentID,WORequestNo,WORequestDate) b on" +
                                " a.WORequestNo = b.WORequestNo and a.WORequestDate = b.WORequestDate " +

                                " where a.DocumentID = 'INDENTSERVICE' and ((a.createuser='******'  and a.DocumentStatus between 2 and 98 ) " +
                                " or (a.ForwarderList like '%" + userList + "%' and a.DocumentStatus between 2 and 98 and a.ForwardUser <> '" + Login.userLoggedIn + "')" +
                                " or (a.commentStatus like '%" + acStr + "%' and a.DocumentStatus between 1 and 98)) and a.Status not in (7,98) order by a.TemporaryDate desc,a.DocumentID asc,a.TemporaryNo desc";

                string query3 = "select a.RowID, a.DocumentID, a.DocumentName,a.TemporaryNo,a.TemporaryDate," +
                                " a.WORequestNo,a.WORequestDate,a.ReferenceInternalOrder,a.CustomerID,a.CustomerName,a.CurrencyID,a.CurrencyName,a.StartDate,a.TargetDate,a.PaymentTerms,a.PaymentMode," +
                                " a.ServiceValue,a.TaxAmount,a.TotalAmount,a.Remarks, " +
                                " a.Status,a.DocumentStatus,a.CreateTime,a.CreateUser,a.ForwardUser,a.ApproveUser,a.CreatorName,a.ForwarderName,a.ApproverName,a.CommentStatus,a.ForwarderList " +
                                " ,a.ExchangeRate,a.ServiceValueINR,a.TaxAmountINR,a.TotalAmountINR,b.NoFound,a.ContractorReference from ViewWORequestHeader a left outer join " +
                                " (select DocumentID,WORequestNo,WORequestDate,COUNT(*) as NoFound from WOHeader where Status = 1 and DocumentStatus = 99 group by DocumentID,WORequestNo,WORequestDate) b on" +
                                " a.WORequestNo = b.WORequestNo and a.WORequestDate = b.WORequestDate " +

                                " where a.DocumentID = 'INDENTSERVICE' and ((a.createuser='******'" +
                                " or a.ForwarderList like '%" + userList + "%'" +
                                " or a.commentStatus like '%" + acStr + "%'" +
                                " or a.approveUser='******')" +
                                " and a.DocumentStatus = 99) and a.status = 1 order by a.WORequestDate desc,a.DocumentID asc,a.WORequestNo desc";
                string query6 = "select a.RowID, a.DocumentID, a.DocumentName,a.TemporaryNo,a.TemporaryDate," +
                                " a.WORequestNo,a.WORequestDate,a.ReferenceInternalOrder,a.CustomerID,a.CustomerName,a.CurrencyID,a.CurrencyName,a.StartDate,a.TargetDate,a.PaymentTerms,a.PaymentMode," +
                                " a.ServiceValue,a.TaxAmount,a.TotalAmount,a.Remarks, " +
                                " a.Status,a.DocumentStatus,a.CreateTime,a.CreateUser,a.ForwardUser,a.ApproveUser,a.CreatorName,a.ForwarderName,a.ApproverName,a.CommentStatus,a.ForwarderList " +
                                " ,a.ExchangeRate,a.ServiceValueINR,a.TaxAmountINR,a.TotalAmountINR,b.NoFound,a.ContractorReference from ViewWORequestHeader a left outer join " +
                                " (select DocumentID,WORequestNo,WORequestDate,COUNT(*) as NoFound from WOHeader where Status = 1 and DocumentStatus = 99 group by DocumentID,WORequestNo,WORequestDate) b on" +
                                " a.WORequestNo = b.WORequestNo and a.WORequestDate = b.WORequestDate " +

                                " where a.DocumentID = 'INDENTSERVICE' and  a.DocumentStatus = 99 and a.status = 1  order by a.WORequestDate desc,a.DocumentID asc,a.WORequestNo desc";
                SqlConnection conn  = new SqlConnection(Login.connString);
                string        query = "";
                switch (opt)
                {
                case 1:
                    query = query1;
                    break;

                case 2:
                    query = query2;
                    break;

                case 3:
                    query = query3;
                    break;

                case 6:
                    query = query6;
                    break;

                default:
                    query = "";
                    break;
                }
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ish               = new indentserviceheader();
                    ish.RowID         = reader.GetInt32(0);
                    ish.DocumentID    = reader.GetString(1);
                    ish.DocumentName  = reader.GetString(2);
                    ish.TemporaryNo   = reader.GetInt32(3);
                    ish.TemporaryDate = reader.GetDateTime(4);
                    ish.WORequestNo   = reader.GetInt32(5);
                    if (!reader.IsDBNull(6))
                    {
                        ish.WORequestDate = reader.GetDateTime(6);
                    }
                    else
                    {
                        ish.WORequestDate = DateTime.Parse("1900-01-01");
                    }
                    ish.ReferenceInternalOrder = reader.IsDBNull(7) ? " " : reader.GetString(7);
                    ish.CustomerID             = reader.GetString(8);
                    ish.CustomerName           = reader.GetString(9);
                    ish.CurrencyID             = reader.GetString(10);
                    ish.CurrencyName           = reader.GetString(11);
                    ish.StartDate      = reader.GetDateTime(12);
                    ish.TargetDate     = reader.GetDateTime(13);
                    ish.PaymentTerms   = reader.GetString(14);
                    ish.PaymentMode    = reader.GetString(15);
                    ish.ServiceValue   = reader.GetDouble(16);
                    ish.TaxAmount      = reader.GetDouble(17);
                    ish.TotalAmount    = reader.GetDouble(18);
                    ish.Remarks        = reader.GetString(19);
                    ish.Status         = reader.GetInt32(20);
                    ish.DocumentStatus = reader.GetInt32(21);
                    ish.CreateTime     = reader.GetDateTime(22);
                    ish.CreateUser     = reader.GetString(23);
                    ish.ForwardUser    = reader.GetString(24);
                    ish.ApproveUser    = reader.GetString(25);
                    ish.CreatorName    = reader.GetString(26);
                    ish.ForwarderName  = reader.GetString(27);
                    ish.ApproverName   = reader.GetString(28);
                    if (!reader.IsDBNull(29))
                    {
                        ish.CommentStatus = reader.GetString(29);
                    }
                    else
                    {
                        ish.CommentStatus = "";
                    }
                    if (!reader.IsDBNull(30))
                    {
                        ish.ForwarderList = reader.GetString(30);
                    }
                    else
                    {
                        ish.ForwarderList = "";
                    }
                    ish.ExchangeRate        = reader.GetDecimal(31);
                    ish.ServiceValueINR     = reader.GetDouble(32);
                    ish.TaxAmountINR        = reader.GetDouble(33);
                    ish.TotalAmountINR      = reader.GetDouble(34);
                    ish.NoOfWOFound         = reader.IsDBNull(35)?0:reader.GetInt32(35);
                    ish.ContractorReference = reader.IsDBNull(36) ? "" : reader.GetString(36);
                    ISHeaders.Add(ish);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying Indent Service Header Details");
            }
            return(ISHeaders);
        }