Esempio n. 1
0
        //----
        public Boolean updateInventoryForApproveStockDetail(gatepassheader gtnh, List <gatepassdetail> getPDet)
        {
            Boolean status    = true;
            string  utString  = "";
            string  updateSQL = "";

            try
            {
                foreach (gatepassdetail gtnd in getPDet)
                {
                    double quant = gtnd.Quantity;
                    int    RefNo = gtnd.refNo;
                    updateSQL = "update Stock set  " +
                                " PresentStock=" + "( (select PresentStock from Stock where RowID = " + RefNo + ")-" + quant + ")" +
                                " where RowID=" + RefNo;

                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    MessageBox.Show("failed to Update In Reference Number Wise getpassdetail in stock");
                    status = false;
                }
            }
            catch (Exception ex)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 2
0
        public Boolean AcceptGatePass(gatepassheader gtnh)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update InventoryGatepassHeader set " +
                                   "AcceptedUser='******'" +
                                   ", AcceptanceDate=convert(date, getdate())" +
                                   ", AcceptanceStatus= 1" +
                                   ", commentStatus='" + gtnh.CommentStatus + "'" +
                                   " where DocumentID='" + gtnh.DocumentID + "'" +
                                   " and TemporaryNo=" + gtnh.TemporaryNo +
                                   " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 3
0
        public Boolean AcceptReturningQnatGatePass(gatepassheader gtnh, List <gatepassdetail> gplist)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update InventoryGatepassHeader set ReturnStatus= 0 " +
                                   " where DocumentID='" + gtnh.DocumentID + "'" +
                                   " and TemporaryNo=" + gtnh.TemporaryNo +
                                   " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                foreach (gatepassdetail gtnd in gplist)
                {
                    updateSQL = "update InventoryGatepassDetail " +
                                "set QuantityReturned = " + (gtnd.ReturningQuantity + gtnd.ReturnedQuantity) +
                                " , QuantityReturning = 0" +
                                " where RowID = " + gtnd.RowID;
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) +
                               Main.QueryDelimiter;

                    double quant = gtnd.ReturningQuantity;
                    int    RefNo = gtnd.refNo;
                    updateSQL = "update Stock set  " +
                                " PresentStock=" + "( (select isnull(PresentStock,0) from Stock where RowID = " + RefNo + ")+" + quant + ")" +
                                " where RowID=" + RefNo;

                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                               Main.QueryDelimiter;

                    updateSQL = "update Stock set  " +
                                " IssueQuantity=" + "( (select isnull(IssueQuantity,0) from Stock where RowID = " + RefNo + ")-" + quant + ")" +
                                " where RowID=" + RefNo;

                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                               Main.QueryDelimiter;
                }


                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 4
0
        //public Boolean forwardGTN(getpassheader gtnh)
        //{
        //    Boolean status = true;
        //    string utString = "";
        //    try
        //    {
        //        string updateSQL = "update getpassheader set DocumentStatus=" + (gtnh.DocumentStatus + 1) +
        //            ", ReceiveStatus = " + gtnh.ReceiveStatus +
        //            ", forwardUser='******'" +
        //            ", commentStatus='" + gtnh.CommentStatus + "'" +
        //            ", ForwarderList='" + gtnh.ForwarderList + "'" +
        //            " where DocumentID='" + gtnh.DocumentID + "'" +
        //            " and TemporaryNo=" + gtnh.TemporaryNo +
        //            " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
        //        utString = utString + updateSQL + Main.QueryDelimiter;
        //        utString = utString +
        //        ActivityLogDB.PrepareActivityLogQquerString("update", "getpassheader", "", updateSQL) +
        //        Main.QueryDelimiter;
        //        if (!UpdateTable.UT(utString))
        //        {
        //            status = false;
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        status = false;
        //    }
        //    return status;
        //}
        //public Boolean reverseGTN(getpassheader gtnh)
        //{
        //    Boolean status = true;
        //    string utString = "";
        //    try
        //    {
        //        string updateSQL = "update getpassheader set DocumentStatus=" + gtnh.DocumentStatus +
        //            ", forwardUser='******'" +
        //            ", commentStatus='" + gtnh.CommentStatus + "'" +
        //            ", ForwarderList='" + gtnh.ForwarderList + "'" +
        //             ", ReceiveStatus=" + gtnh.ReceiveStatus +
        //            " where DocumentID='" + gtnh.DocumentID + "'" +
        //            " and TemporaryNo=" + gtnh.TemporaryNo +
        //            " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
        //        utString = utString + updateSQL + Main.QueryDelimiter;
        //        utString = utString +
        //        ActivityLogDB.PrepareActivityLogQquerString("update", "getpassheader", "", updateSQL) +
        //        Main.QueryDelimiter;
        //        if (!UpdateTable.UT(utString))
        //        {
        //            status = false;
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        status = false;
        //    }
        //    return status;
        //}
        public Boolean ApproveGatePass(gatepassheader gtnh, List <gatepassdetail> gplist)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update InventoryGatepassHeader set DocumentStatus=99, status=1 " +
                                   ", ApproveUser ='******'" +
                                   ", ForwarderList ='" + gtnh.ForwarderList + "'" +
                                   ", commentStatus='" + gtnh.CommentStatus + "'" +
                                   ", GatePassNo =" + gtnh.GatePassNo +
                                   ", GatePassDate =convert(date, getdate())" +
                                   " where DocumentID='" + gtnh.DocumentID + "'" +
                                   " and TemporaryNo=" + gtnh.TemporaryNo +
                                   " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;
                foreach (gatepassdetail gtnd in gplist)
                {
                    double quant = gtnd.Quantity;
                    int    RefNo = gtnd.refNo;
                    updateSQL = "update Stock set  " +
                                " PresentStock=" + "( (select PresentStock from Stock where RowID = " + RefNo + ")-" + quant + ")" +
                                " where RowID=" + RefNo;

                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                               Main.QueryDelimiter;

                    updateSQL = "update Stock set  " +
                                "IssueQuantity=" + "( (select isnull(IssueQuantity,0) from Stock where RowID = " + RefNo + ")+" + quant + ")" +
                                " where RowID=" + RefNo;

                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 5
0
        public Boolean validategetpassheader(gatepassheader gtnh)
        {
            Boolean status = true;

            if (gtnh.FromOffice == gtnh.ToOffice)
            {
                MessageBox.Show("From Location and To Location Should not be same");
                return(false);
            }
            if (gtnh.FromOffice == null)
            {
                return(false);
            }
            if (gtnh.ToOffice == null)
            {
                return(false);
            }
            return(status);
        }
Esempio n. 6
0
        public Boolean ReturnGatePass(gatepassheader gtnh, List <gatepassdetail> gplist)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update InventoryGatepassHeader set ReturnStatus=" + (gtnh.ReturnStatus + 1) +
                                   " where DocumentID='" + gtnh.DocumentID + "'" +
                                   " and TemporaryNo=" + gtnh.TemporaryNo +
                                   " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                foreach (gatepassdetail gtnd in gplist)
                {
                    updateSQL = "update InventoryGatepassDetail " +
                                "set QuantityReturning = " + gtnd.ReturningQuantity +
                                " where RowID = " + gtnd.RowID;
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) +
                               Main.QueryDelimiter;
                }


                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 7
0
        public Boolean insertInvGatePassHEaderAndDetail(gatepassheader gtnh, List <gatepassdetail> getpassdetails)
        {
            Boolean status    = true;
            string  utString  = "";
            string  updateSQL = "";

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

                updateSQL = "insert into InventoryGatepassHeader " +
                            "(DocumentID,TemporaryNo,TemporaryDate,GatePassNo,GatePassDate,FromOffice,ToOffice,CustomerID,Remarks,SpecialNotes," +
                            "Comments,CommentStatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," +
                            "ForwarderList,Status,DocumentStatus,CreateTime,CreateUser)" +
                            " values (" +
                            "'" + gtnh.DocumentID + "'," +
                            gtnh.TemporaryNo + "," +
                            "'" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                            gtnh.GatePassNo + "," +
                            "'" + gtnh.GatePassDate.ToString("yyyy-MM-dd") + "'," +
                            "'" + gtnh.FromOffice + "'," +
                            "'" + gtnh.ToOffice + "'," +
                            "'" + gtnh.CustomerID + "'," +
                            "'" + gtnh.Remarks + "'," +
                            "'" + gtnh.SpecialNotes + "'," +
                            "'" + gtnh.Comments + "'," +
                            "'" + gtnh.CommentStatus + "'," +
                            "'" + DateTime.Parse("1900-01-01").ToString("yyyy-MM-dd") + "'," +
                            "'" + gtnh.AcceptedUser + "'," +
                            +gtnh.AcceptanceStatus + "," +
                            "'" + gtnh.ForwarderList + "'," +
                            gtnh.Status + "," +
                            gtnh.DocumentStatus + "," +
                            "GETDATE()" + "," +
                            "'" + Login.userLoggedIn + "')";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                updateSQL = "Delete from InventoryGatepassDetail where DocumentID='" + gtnh.DocumentID + "'" +
                            " and TemporaryNo=" + gtnh.TemporaryNo +
                            " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("delete", "InventoryGatepassDetail", "", updateSQL) +
                           Main.QueryDelimiter;
                foreach (gatepassdetail gtnd in getpassdetails)
                {
                    updateSQL = "insert into InventoryGatepassDetail " +
                                "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,Value,QuantityReturning,QuantityReturned,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity, " +
                                "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " +
                                "values ('" + gtnh.DocumentID + "'," +
                                gtnh.TemporaryNo + "," +
                                "'" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.StockitemID + "'," +
                                "'" + gtnd.ModelNo + "'," +
                                gtnd.Quantity + "," + gtnd.Value + "," + gtnd.ReturningQuantity + "," + gtnd.ReturnedQuantity + "," + gtnd.MRNNo + "," +
                                "'" + gtnd.MRNDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.BatchNo + "'," +
                                "'" + gtnd.ExpiryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.SerialNo + "'," +
                                gtnd.PurchaseQuantity + "," +
                                gtnd.PurchasePrice + "," +
                                gtnd.PurchaseTax + "," +
                                "'" + gtnd.SupplierID + "'," +
                                +gtnd.refNo + ")";
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                }
            }
            catch (Exception ex)
            {
                status = false;
                MessageBox.Show("Transaction Exception Occured");
            }
            return(status);
        }
Esempio n. 8
0
        public Boolean updateInvGatePassHEaderAndDetail(gatepassheader gtnh, gatepassheader prevgtnh, List <gatepassdetail> getpassdetails)
        {
            Boolean status   = true;
            string  utString = "";

            try
            {
                string updateSQL = "update InventoryGatepassHeader set FromOffice='" + gtnh.FromOffice +
                                   "',ToOffice='" + gtnh.ToOffice +
                                   "', CustomerID ='" + gtnh.CustomerID +
                                   "', Remarks ='" + gtnh.Remarks +
                                   "', SpecialNotes ='" + gtnh.SpecialNotes +
                                   "', CommentStatus='" + gtnh.CommentStatus +
                                   "', Comments='" + gtnh.Comments +
                                   "', ForwarderList='" + gtnh.ForwarderList +
                                   "' where DocumentID='" + gtnh.DocumentID + "'" +
                                   " and TemporaryNo=" + gtnh.TemporaryNo +
                                   " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) +
                           Main.QueryDelimiter;

                updateSQL = "Delete from InventoryGatepassDetail where DocumentID='" + gtnh.DocumentID + "'" +
                            " and TemporaryNo=" + gtnh.TemporaryNo +
                            " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("delete", "InventoryGatepassDetail", "", updateSQL) +
                           Main.QueryDelimiter;
                foreach (gatepassdetail gtnd in getpassdetails)
                {
                    updateSQL = "insert into InventoryGatepassDetail " +
                                "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,Value,QuantityReturning,QuantityReturned,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity, " +
                                "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " +
                                "values ('" + gtnd.DocumentID + "'," +
                                gtnd.TemporaryNo + "," +
                                "'" + gtnd.TemporaryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.StockitemID + "'," +
                                "'" + gtnd.ModelNo + "'," +
                                gtnd.Quantity + "," + gtnd.Value + "," + gtnd.ReturningQuantity + "," + gtnd.ReturnedQuantity + "," + gtnd.MRNNo + "," +
                                "'" + gtnd.MRNDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.BatchNo + "'," +
                                "'" + gtnd.ExpiryDate.ToString("yyyy-MM-dd") + "'," +
                                "'" + gtnd.SerialNo + "'," +
                                gtnd.PurchaseQuantity + "," +
                                gtnd.PurchasePrice + "," +
                                gtnd.PurchaseTax + "," +
                                "'" + gtnd.SupplierID + "'," +
                                +gtnd.refNo + ")";
                    utString = utString + updateSQL + Main.QueryDelimiter;
                    utString = utString +
                               ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) +
                               Main.QueryDelimiter;
                }
                if (!UpdateTable.UT(utString))
                {
                    status = false;
                    MessageBox.Show("Transaction Exception Occured");
                }
            }
            catch (Exception ex)
            {
                status = false;
            }
            return(status);
        }
Esempio n. 9
0
        //public Boolean updateGTNInStock(List<getpassdetail> gtndList, string store)
        //{
        //    Boolean status = true;
        //    try
        //    {
        //        foreach (getpassdetail gtnd in gtndList)
        //        {
        //            if (!CheckStockAvailability(gtnd.refNo, gtnd.Quantity))
        //            {
        //                status = false;
        //                break;
        //            }
        //        }
        //        if (status)
        //        {
        //            //stock available. select rows from stock table. update stcok table. insert GTN records in stock table

        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        status = false;
        //    }
        //    return status;
        //}
        //public Boolean CheckStockAvailability(int stockRefNo, double Qunt)
        //{
        //    Boolean status = false;

        //    try
        //    {
        //        SqlConnection conn = new SqlConnection(Login.connString);
        //        string query = "select PresentStock" +
        //           " from Stock where RowID =" + stockRefNo;
        //        SqlCommand cmd = new SqlCommand(query, conn);
        //        conn.Open();
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if (reader.Read())
        //        {
        //            Double aqty = reader.GetDouble(0);
        //            if (aqty >= Qunt)
        //                status = true;
        //        }
        //        conn.Close();
        //    }
        //    catch (Exception)
        //    {
        //        MessageBox.Show("Error querying stock quantity");
        //        status = false;
        //    }
        //    return status;
        //}

        public static List <gatepassdetail> getGatePassdetail(gatepassheader gtnh)
        {
            gatepassdetail        gtnd;
            List <gatepassdetail> getpassdetail = new List <gatepassdetail>();

            try
            {
                string        query = "";
                SqlConnection conn  = new SqlConnection(Login.connString);
                query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,StockItemID,StockItemName,ModelNo, ModelName,Quantity,QuantityReturning, " +
                        "QuantityReturned,MRNNo,MRNDate,BatchNo,SerialNo,ExpiryDate,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,SupplierName,StockReferenceNo,Value " +
                        "from ViewInventoryGatepassDetail " +
                        "where DocumentID='" + gtnh.DocumentID + "'" +
                        " and TemporaryNo=" + gtnh.TemporaryNo +
                        " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'" +
                        " order by StockItemID";
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    gtnd               = new gatepassdetail();
                    gtnd.RowID         = reader.GetInt32(0);
                    gtnd.DocumentID    = reader.GetString(1);
                    gtnd.TemporaryNo   = reader.GetInt32(2);
                    gtnd.TemporaryDate = reader.GetDateTime(3).Date;
                    gtnd.StockitemID   = reader.GetString(4);
                    gtnd.StockItemName = reader.GetString(5);
                    gtnd.ModelNo       = reader.IsDBNull(6)?"NA":reader.GetString(6);
                    gtnd.ModelName     = reader.IsDBNull(7) ? "NA":reader.GetString(7);
                    gtnd.Quantity      = reader.GetDouble(8);

                    gtnd.ReturningQuantity = reader.GetDouble(9);
                    gtnd.ReturnedQuantity  = reader.GetDouble(10);

                    gtnd.MRNNo = reader.IsDBNull(11)? 0:reader.GetInt32(11);
                    if (reader.IsDBNull(12))
                    {
                        gtnd.MRNDate = DateTime.Parse("1900-01-01");
                    }
                    else
                    {
                        gtnd.MRNDate = reader.GetDateTime(12).Date;
                    }
                    gtnd.BatchNo  = reader.IsDBNull(13) ? "NA" : reader.GetString(13);
                    gtnd.SerialNo = reader.IsDBNull(14) ? "NA" : reader.GetString(14);
                    if (reader.IsDBNull(15))
                    {
                        gtnd.ExpiryDate = DateTime.Parse("1900-01-01");
                    }
                    else
                    {
                        gtnd.ExpiryDate = reader.GetDateTime(15);
                    }
                    gtnd.PurchaseQuantity = reader.GetDouble(16);
                    gtnd.PurchasePrice    = reader.GetDouble(17);
                    gtnd.PurchaseTax      = reader.GetDouble(18);
                    gtnd.SupplierID       = reader.IsDBNull(19)? "NA" : reader.GetString(19);
                    gtnd.SupplierName     = reader.IsDBNull(20)?"NA" : reader.GetString(20);
                    if (reader.IsDBNull(21))
                    {
                        gtnd.refNo = 0;
                    }
                    else
                    {
                        gtnd.refNo = reader.GetInt32(21);
                    }
                    gtnd.Value = reader.IsDBNull(22) ? 0 : reader.GetDouble(22);
                    getpassdetail.Add(gtnd);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying Gate Pass Details");
            }
            return(getpassdetail);
        }
Esempio n. 10
0
        public List <gatepassheader> getFilteredGPHeader(string userList, int opt, string userCommentStatusString)
        {
            //approved user comment status string
            string acStr = "";

            try
            {
                acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
            }
            catch (Exception ex)
            {
                acStr = "";
            }
            //-----
            gatepassheader        gheader;
            List <gatepassheader> gheaderlist = new List <gatepassheader>();

            try
            {
                string query1 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
                                " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," +
                                "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" +
                                " from ViewInventoryGatepassHeader" +
                                " where ((forwardUser='******' and DocumentStatus between 2 and 98) " +
                                " or (createuser='******' and DocumentStatus=1)" +
                                " or (commentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98))  and Status not in (7,98) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc";

                //string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
                //    " GetPassNO,GetPassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," +
                //    "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus" +
                //    " from ViewInventoryGatepassHeader" +
                //   " where ((createuser='******'  and DocumentStatus between 2 and 98 ) " +
                //    " or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "')" +
                //    " or (commentStatus like '%" + acStr + "%' and DocumentStatus between 1 and 98))  and Status not in (7,98) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc";
                string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
                                " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," +
                                "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" +
                                " from ViewInventoryGatepassHeader" +
                                " where  DocumentStatus = 99 and Status = 1  order by GatePassDate desc,DocumentID asc,GatePassNO desc";

                string query4 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," +
                                " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," +
                                "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" +
                                " from ViewInventoryGatepassHeader" +
                                " where  DocumentStatus = 99 and Status = 1  and ForwarderList like '%" + userList + "%' order by GatePassDate desc,DocumentID asc,GatePassNO desc";
                SqlConnection conn  = new SqlConnection(Login.connString);
                string        query = "";
                switch (opt)
                {
                case 1:
                    query = query1;
                    break;

                case 6:
                    query = query6;
                    break;

                case 4:
                    query = query4;
                    break;

                default:
                    query = "";
                    break;
                }
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    gheader                  = new gatepassheader();
                    gheader.RowID            = reader.GetInt32(0);
                    gheader.DocumentID       = reader.GetString(1);
                    gheader.DocumentName     = reader.GetString(2);
                    gheader.TemporaryNo      = reader.GetInt32(3);
                    gheader.TemporaryDate    = reader.GetDateTime(4);
                    gheader.GatePassNo       = reader.GetInt32(5);
                    gheader.GatePassDate     = reader.GetDateTime(6);
                    gheader.FromOffice       = reader.GetString(7);
                    gheader.FromOfficeName   = reader.GetString(8);
                    gheader.ToOffice         = reader.GetString(9);
                    gheader.ToOfficeName     = reader.GetString(10);
                    gheader.CustomerID       = reader.IsDBNull(11)? "" : reader.GetString(11);
                    gheader.CustomerName     = reader.IsDBNull(12) ? "" : reader.GetString(12);
                    gheader.Comments         = reader.GetString(13);
                    gheader.CommentStatus    = reader.GetString(14);
                    gheader.AcceptanceDate   = reader.GetDateTime(15);
                    gheader.AcceptedUser     = reader.IsDBNull(16)?"":reader.GetString(16);
                    gheader.AcceptanceStatus = reader.GetInt32(17);
                    gheader.CreateUser       = reader.GetString(18);
                    gheader.CreateTime       = reader.GetDateTime(19);
                    gheader.ForwardUser      = reader.IsDBNull(20) ? "" : reader.GetString(20);
                    gheader.ApproveUser      = reader.IsDBNull(21) ? "" : reader.GetString(21);
                    gheader.ForwarderList    = reader.GetString(22);
                    gheader.Status           = reader.GetInt32(23);
                    gheader.DocumentStatus   = reader.GetInt32(24);
                    gheader.ReturnStatus     = reader.IsDBNull(25) ? 0 : reader.GetInt32(25);
                    gheader.Remarks          = reader.IsDBNull(26) ? "" : reader.GetString(26);
                    gheader.SpecialNotes     = reader.IsDBNull(27) ? "" : reader.GetString(27);
                    gheaderlist.Add(gheader);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying GTN Header Data");
            }
            return(gheaderlist);
        }