Example #1
0
        public void updateRefNoWiseGTNDetailInStock(double Quant, int refNo, gtndetail gtnd, gtnheader gtnh)
        {
            //Boolean status = true;
            string updateSQL = "";
            string utString  = "";

            try
            {
                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;


                updateSQL = "insert into Stock " +
                            "(FYID,StockItemID,ModelNo,InwardDocumentID,InwardDocumentNo,InwardDocumentDate,InwardQuantity,PresentStock,MRNNo,MRNDate," +
                            "BatchNo,ExpiryDate,SerialNo,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,StoreLocation," +
                            "CreateTime,CreateUser)" +
                            " values (" +
                            "'" + Main.currentFY + "'," +
                            "'" + gtnd.StockitemID + "'," +
                            "'" + gtnd.ModelNo + "'," +
                            "'" + "GTN" + "'," +
                            gtnh.GTNNo + "," +
                            "'" + gtnh.GTNDate.ToString("yyyy-MM-dd") + "'," +
                            gtnd.Quantity + "," +
                            gtnd.Quantity + "," +
                            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 + "'," +
                            "'" + gtnh.ToLocation + "'," +
                            "GETDATE()" + "," +
                            "'" + Login.userLoggedIn + "')";
                utString = utString + updateSQL + Main.QueryDelimiter;
                utString = utString +
                           ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
                           Main.QueryDelimiter;
                if (!UpdateTable.UT(utString))
                {
                    //status = false;
                    MessageBox.Show("failed to Update In Reference Number Wise GTNDetail in stock");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("failed to Update In Reference Number Wise SIDetail in stock");
                return;
            }
            //return status;
        }
Example #2
0
        //---

        public static List <gtndetail> getGTNDetail(gtnheader gtnh)
        {
            gtndetail        gtnd;
            List <gtndetail> GTNDetail = new List <gtndetail>();

            try
            {
                string        query = "";
                SqlConnection conn  = new SqlConnection(Login.connString);
                query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,StockItemID,StockItemName,ModelNo, ModelName,Quantity, " +
                        "MRNNo,MRNDate,BatchNo,SerialNo,ExpiryDate,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,SupplierName,StockReferenceNo " +
                        "from ViewGTNDetail " +
                        "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 gtndetail();
                    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.MRNNo         = reader.IsDBNull(9)? 0:reader.GetInt32(9);
                    if (reader.IsDBNull(10))
                    {
                        gtnd.MRNDate = DateTime.Parse("01-01-1900");
                    }
                    else
                    {
                        gtnd.MRNDate = reader.GetDateTime(10).Date;
                    }
                    gtnd.BatchNo  = reader.IsDBNull(11) ? "NA" : reader.GetString(11);
                    gtnd.SerialNo = reader.IsDBNull(12) ? "NA" : reader.GetString(12);
                    if (reader.IsDBNull(13))
                    {
                        gtnd.ExpiryDate = DateTime.Parse("01-01-1900");
                    }
                    else
                    {
                        gtnd.ExpiryDate = reader.GetDateTime(13);
                    }
                    gtnd.PurchaseQuantity = reader.GetDouble(14);
                    gtnd.PurchasePrice    = reader.GetDouble(15);
                    gtnd.PurchaseTax      = reader.GetDouble(16);
                    gtnd.SupplierID       = reader.IsDBNull(17)? "NA" : reader.GetString(17);
                    gtnd.SupplierName     = reader.IsDBNull(18)?"NA" : reader.GetString(17);
                    if (reader.IsDBNull(13))
                    {
                        gtnd.refNo = 0;
                    }
                    else
                    {
                        gtnd.refNo = reader.GetInt32(19);
                    }
                    GTNDetail.Add(gtnd);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying GTN Details");
            }
            return(GTNDetail);
        }